/*

 Program tables.do makes tables.

 Kumler, Verhoogen, Frias "Enlisting Employees ..." REStat forthcoming

*/


/*
Notes:

1.

*/

***************** housekeeping ******************;

#delimit;
set more off;

do ${code}housekeeping.do;
do ${code}programs.do;

set mem 8g;
set processors 8;

********************************************************************;
********************************************************************;
************ Tables in main text ***********************************;
********************************************************************;
********************************************************************;


******************************************************************;
******* Tables 1 & A5 (tpensim): Pension Simulations *************;
******************************************************************;

*** get benefit files ready;

clear all;
use ${work}imss_benefits;
sort year qtr;
tab qtr;
tempfile d1;
save `d1', replace;

clear all;
use ${raw}imss_benefits_sched_ev_2011_09_26.dta;
sort max_mw;
tempfile d2;
save `d2', replace;


*** get minimum wages file ready;

* Here I am just keeping minimum wages for zone A (Mexico City). May
  want to come back to this;

clear all;
use ${work}minwages_wtopcodes;
keep if zona=="A";
keep year qtr rsalmin salmin cpi;
sort year qtr;
tab qtr;
tempfile d3;
save `d3', replace;

*save local for 1997 minimum wage to use in calculating post-97 benefits;

sum salmin if year==1997;
scalar salmin97 = r(mean);
sum rsalmin if year==1997;
scalar rsalmin97 = r(mean);
sum cpi if year==1997;
local cpi97 = r(mean);

*** make fake wage data;

** this wage data is multiples of 50 real pesos, going from 0 to 1100
   since 25X the real minimum wage (43.85) is 1096.25.;

local ninds = 23; * number of fake individuals;

clear;
set obs `ninds';
gen sal = (_n-1)*50;
replace sal = rsalmin97 if _n==1; *make the first observation equal to the min wage;
replace sal = 25*rsalmin97 if _n==23; * mke the last observation equal to the top code;
gen sal_mw = sal/rsalmin97;

*find upper bound of that salary range for merging in schedule;
gen sal_mw_round=round(sal_mw,.01);
gen max_mw=ceil(4*sal_mw_round)/4;

sort max_mw;

*** merge in benefits schedule;
merge max_mw using `d2';
tab _merge;
drop if _merge==2;
drop _merge;

*** enter by hand the benefits for individuals making below the minimum
    wage or above 6 times the minimum wage;

replace avg=80 if max_mw<1;
replace add_yr=0.563 if max_mw<1;

replace avg=13 if max_mw>6;
replace add_yr=2.45 if max_mw>6;

drop sal_mw_round max_mw min_mw;

local inf=1.1365;
local sal1=1;
local sal2=`sal1'*`inf';
local sal3=`sal2'*`inf';
local sal4=`sal3'*`inf';
local sal5=`sal4'*`inf';
local nsal_5yrs = ((`sal1'+`sal2'+`sal3'+`sal4'+`sal5')/5)/`sal5';

di "`sal1'";
di "`sal2'";
di "`sal3'";
di "`sal4'";
di "`sal5'";
di "`nsal_5yrs'";

gen nsal_5yrs=`nsal_5yrs'*sal;
sum nsal_5yrs;



*** Calculate monthly pension for individuals retiring with average wage;

gen benefit95_avg = 1.15*nsal_5yrs*(avg/100);
replace benefit95_avg = 25*(avg/100) if nsal_5yrs/rsalmin97>25;
gen benefit95_add_yr = 1.15*nsal_5yrs*(add_yr/100);
replace benefit95_add_yr = 25*(add_yr/100) if nsal_5yrs/rsalmin97>25;

gen pension95_5yr_norm = 0;
gen pension95_10yr_norm = benefit95_avg;
replace pension95_10yr_norm = rsalmin97 if pension95_10yr_norm < rsalmin97;
gen pension95_15yr_norm = benefit95_avg + 5*benefit95_add_yr;
replace pension95_15yr_norm = rsalmin97 if pension95_15yr_norm < rsalmin97;
gen pension95_20yr_norm = benefit95_avg + 10*benefit95_add_yr;
replace pension95_20yr_norm = rsalmin97 if pension95_20yr_norm < rsalmin97;
gen pension95_25yr_norm = benefit95_avg + 14.42307692*benefit95_add_yr;
replace pension95_25yr_norm = rsalmin97 if pension95_25yr_norm < rsalmin97;
gen pension95_30yr_norm = benefit95_avg + 20*benefit95_add_yr;
replace pension95_30yr_norm = rsalmin97 if pension95_30yr_norm < rsalmin97;
gen pension95_35yr_norm = benefit95_avg + 25*benefit95_add_yr;
replace pension95_35yr_norm = rsalmin97 if pension95_35yr_norm < rsalmin97;
gen pension95_40yr_norm = benefit95_avg + 30*benefit95_add_yr;
replace pension95_40yr_norm = rsalmin97 if pension95_40yr_norm < rsalmin97;

gen pension95_5yr_early = 0;
gen pension95_10yr_early = 0.75*(benefit95_avg);
replace pension95_10yr_early = rsalmin97 if pension95_10yr_early < rsalmin97;
gen pension95_15yr_early = 0.75*(benefit95_avg + 5*benefit95_add_yr);
replace pension95_15yr_early = rsalmin97 if pension95_15yr_early < rsalmin97;
gen pension95_20yr_early = 0.75*(benefit95_avg + 10*benefit95_add_yr);
replace pension95_20yr_early = rsalmin97 if pension95_20yr_early < rsalmin97;
gen pension95_25yr_early = 0.75*(benefit95_avg + 14.42307692*benefit95_add_yr);
replace pension95_25yr_early = rsalmin97 if pension95_25yr_early < rsalmin97;
gen pension95_30yr_early = 0.75*(benefit95_avg + 20*benefit95_add_yr);
replace pension95_30yr_early = rsalmin97 if pension95_30yr_early < rsalmin97;
gen pension95_35yr_early = 0.75*(benefit95_avg + 25*benefit95_add_yr);
replace pension95_35yr_early = rsalmin97 if pension95_35yr_early < rsalmin97;
gen pension95_40yr_early = 0.75*(benefit95_avg + 30*benefit95_add_yr);
replace pension95_40yr_early = rsalmin97 if pension95_40yr_early < rsalmin97;

** Calculate Present Value of Pensions;
** Refer to EQ 25 of Aguila paper;
** Assume: inflation loss of minimum wage to be 6.4 percent and discount rate of 1 percent;
** Assume: early retirement at age 60, normal retirement at age 65
** Assume: male life expectancy is 93, female life expectancy is 87;
local I_male = 93;
local I_female = 87;
local I_77le = 77;
local I_70le = 70;
local R_early = 60;
local R_norm = 65;
local n_male_early = `I_male' - `R_early';
local n_male_norm = `I_male' - `R_norm';
local n_female_early = `I_female' - `R_early';
local n_female_norm = `I_female' - `R_norm';
local n_77le_early = `I_77le' - `R_early';
local n_77le_norm = `I_77le' - `R_norm';
local n_70le_early = `I_70le' - `R_early';
local n_70le_norm = `I_70le' - `R_norm';
local delta = 0.06441;
local eta = 0.01;
local r2001 = 1/(1+`eta');
local r1997 = (1-`delta')/(1+`eta');
local r_ir0 = 1.04;
local r_ir1 = 1.08594;

local s_male_early_97 = (1 - `r1997'^(`n_male_early'+1)) / (1-`r1997');
local s_male_norm_97 = (1 - `r1997'^(`n_male_norm'+1)) / (1-`r1997');
local s_female_early_97 = (1 - `r1997'^(`n_female_early'+1)) / (1-`r1997');
local s_female_norm_97 = (1 - `r1997'^(`n_female_norm'+1)) / (1-`r1997');
local s_77le_early_97 = (1 - `r1997'^(`n_77le_early'+1)) / (1-`r1997');
local s_77le_norm_97 = (1 - `r1997'^(`n_77le_norm'+1)) / (1-`r1997');
local s_70le_early_97 = (1 - `r1997'^(`n_70le_early'+1)) / (1-`r1997');
local s_70le_norm_97 = (1 - `r1997'^(`n_70le_norm'+1)) / (1-`r1997');

local s_male_early_01 = (1 - `r2001'^(`n_male_early'+1)) / (1-`r2001');
local s_male_norm_01 = (1 - `r2001'^(`n_male_norm'+1)) / (1-`r2001');
local s_female_early_01 = (1 - `r2001'^(`n_female_early'+1)) / (1-`r2001');
local s_female_norm_01 = (1 - `r2001'^(`n_female_norm'+1)) / (1-`r2001');
local s_77le_early_01 = (1 - `r2001'^(`n_77le_early'+1)) / (1-`r2001');
local s_77le_norm_01 = (1 - `r2001'^(`n_77le_norm'+1)) / (1-`r2001');
local s_70le_early_01 = (1 - `r2001'^(`n_70le_early'+1)) / (1-`r2001');
local s_70le_norm_01 = (1 - `r2001'^(`n_70le_norm'+1)) / (1-`r2001');

foreach sex in male female 77le 70le {;
	foreach r_age in early norm {;
		forval yr=5(5)40 {;

			**Present value of PAYGO pension is pension times 365, then discounted;

			gen pv_paygo_97_`yr'yr_`sex'_`r_age' = 365*pension95_`yr'yr_`r_age'*`s_`sex'_`r_age'_97';
			gen pv_paygo_01_`yr'yr_`sex'_`r_age' = 365*pension95_`yr'yr_`r_age'*`s_`sex'_`r_age'_01';
			gen pv_paygo_9701_`yr'yr_`sex'_`r_age' = 365*pension95_`yr'yr_`r_age'*`s_`sex'_`r_age'_97';
		};
	};
};

**New r for IRA pensions;
local r = 1/(1+`eta');

**Minimum IRA pension is 1*minimum wage per month, so 12*mw annually
  This matches minimum IRA pension in Aguila.;
local min_ira_male_early = 365*rsalmin97*(1 - `r'^(`n_male_early'+1)) / (1-`r');
local min_ira_male_norm = 365*rsalmin97*(1 - `r'^(`n_male_norm'+1)) / (1-`r');
local min_ira_female_early = 365*rsalmin97*(1 - `r'^(`n_female_early'+1)) / (1-`r');
local min_ira_female_norm = 365*rsalmin97*(1 - `r'^(`n_female_norm'+1)) / (1-`r');
local min_ira_77le_early = 365*rsalmin97*(1 - `r'^(`n_77le_early'+1)) / (1-`r');
local min_ira_77le_norm = 365*rsalmin97*(1 - `r'^(`n_77le_norm'+1)) / (1-`r');
local min_ira_70le_early = 365*rsalmin97*(1 - `r'^(`n_70le_early'+1)) / (1-`r');
local min_ira_70le_norm = 365*rsalmin97*(1 - `r'^(`n_70le_norm'+1)) / (1-`r');

foreach sex in male female 77le 70le {;
	foreach r_age in early norm {;
		forval yr=10(5)40 {;
			replace pv_paygo_9701_`yr'yr_`sex'_`r_age' = `min_ira_`sex'_`r_age''
			  if pv_paygo_9701_`yr'yr_`sex'_`r_age' < `min_ira_`sex'_`r_age'';
			replace pv_paygo_9701_`yr'yr_`sex'_`r_age' = pv_paygo_9701_`yr'yr_`sex'_`r_age' / 1000;
			replace pv_paygo_97_`yr'yr_`sex'_`r_age' = pv_paygo_97_`yr'yr_`sex'_`r_age' / 1000;
			replace pv_paygo_01_`yr'yr_`sex'_`r_age' = pv_paygo_01_`yr'yr_`sex'_`r_age' / 1000;
		};
	};
};


foreach sex in male female 77le 70le {;
	foreach r_age in early norm {;

		**Contribution to IRA is 0.065*salary + 0.055*minimum wage per month,
		  so multiply by 12 to find yearly contribution. To calculate
		  value of pension at retirement, multiple this by geometric series formula;
		**To calcualte present value, find the annual payment by dividing the pension
		  wealth by the total number of years paid out, then discount;

		** real interest rate equal to 4.0;
		forval yr=1/24 {;

			gen ira_`yr'yr_`sex'_`r_age'_ir4 = (365*(sal*0.115+0.055*1)*((1 - `r_ir0'^(`yr'+1)) / (1 - `r_ir0'))) - (365*(sal*0.115+0.055*1));
			gen pv_ira_`yr'yr_`sex'_`r_age'_ir4 = (ira_`yr'yr_`sex'_`r_age'_ir4/`n_`sex'_`r_age'')*(1 - `r'^(`n_`sex'_`r_age''+1)) / (1-`r');
			gen pv_ira_`yr'yr_`sex'_`r_age'_ir4_n25 = pv_ira_`yr'yr_`sex'_`r_age'_ir4;
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir4 = `min_ira_`sex'_`r_age'' if pv_ira_`yr'yr_`sex'_`r_age'_ir4<`min_ira_`sex'_`r_age'';
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir4 = pv_ira_`yr'yr_`sex'_`r_age'_ir4 / 1000;
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir4_n25 = pv_ira_`yr'yr_`sex'_`r_age'_ir4_n25 / 1000;
		};

		forval yr=25(5)40 {;
			gen ira_`yr'yr_`sex'_`r_age'_ir4 = 365*(sal*0.115+0.055*1)*((1 - `r_ir0'^(`yr'+1)) / (1 - `r_ir0')) - (365*(sal*0.115+0.055*1));
			gen pv_ira_`yr'yr_`sex'_`r_age'_ir4 = (ira_`yr'yr_`sex'_`r_age'_ir4/`n_`sex'_`r_age'')*(1 - `r'^(`n_`sex'_`r_age''+1)) / (1-`r');
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir4 = `min_ira_`sex'_`r_age'' if pv_ira_`yr'yr_`sex'_`r_age'_ir4<`min_ira_`sex'_`r_age'';
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir4 = pv_ira_`yr'yr_`sex'_`r_age'_ir4 / 1000;

		};

		** real interest rate equal to 8.594;
		forval yr=1/24 {;

			gen ira_`yr'yr_`sex'_`r_age'_ir8 = (365*(sal*0.115+0.055*1)*((1 - `r_ir1'^(`yr'+1)) / (1 - `r_ir1'))) - (365*(sal*0.115+0.055*1));
			gen pv_ira_`yr'yr_`sex'_`r_age'_ir8 = (ira_`yr'yr_`sex'_`r_age'_ir8/`n_`sex'_`r_age'')*(1 - `r'^(`n_`sex'_`r_age''+1)) / (1-`r');
			gen pv_ira_`yr'yr_`sex'_`r_age'_ir8_n25 = pv_ira_`yr'yr_`sex'_`r_age'_ir8;
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir8 = `min_ira_`sex'_`r_age'' if pv_ira_`yr'yr_`sex'_`r_age'_ir8<`min_ira_`sex'_`r_age'';
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir8 = pv_ira_`yr'yr_`sex'_`r_age'_ir8 / 1000;
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir8_n25 = pv_ira_`yr'yr_`sex'_`r_age'_ir8_n25 / 1000;

		};

		forval yr=25(5)40 {;
			gen ira_`yr'yr_`sex'_`r_age'_ir8 = 365*(sal*0.115+0.055*1)*((1 - `r_ir1'^(`yr'+1)) / (1 - `r_ir1')) - (365*(sal*0.115+0.055*1));
			gen pv_ira_`yr'yr_`sex'_`r_age'_ir8 = (ira_`yr'yr_`sex'_`r_age'_ir8/`n_`sex'_`r_age'')*(1 - `r'^(`n_`sex'_`r_age''+1)) / (1-`r');
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir8 = `min_ira_`sex'_`r_age'' if pv_ira_`yr'yr_`sex'_`r_age'_ir8<`min_ira_`sex'_`r_age'';
			replace pv_ira_`yr'yr_`sex'_`r_age'_ir8 = pv_ira_`yr'yr_`sex'_`r_age'_ir8 / 1000;

		};
	};
};

keep sal pv_*;

** The code below determines which pension workers will choose, based on 1997 rules;

** now are assuming 10 years of contributions, so no eligibility for minimum pension under new scheme;
** the difference for years is in the number of years under the new system;
foreach ir in 4 8 {;
	foreach r_age in norm early {;
		foreach sex in male female 77le 70le {;
			foreach yr in 5 10 {;
				gen choice_`yr'yr_`sex'_`r_age'_ir`ir'_10 = .;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_10 = 2 if pv_paygo_97_10yr_`sex'_`r_age' > pv_ira_`yr'yr_`sex'_`r_age'_ir`ir'_n25;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_10 = 1 if pv_paygo_97_10yr_`sex'_`r_age' < pv_ira_`yr'yr_`sex'_`r_age'_ir`ir'_n25;
			};
		};
	};
};

** now are assuming 15 years of contributions, so no eligibility for minimum pension under new scheme;
** the difference for years is in the number of years under the new system;
foreach ir in 4 8 {;
	foreach r_age in norm early {;
		foreach sex in male female 77le 70le {;
			foreach yr in 5 10 15 {;
				gen choice_`yr'yr_`sex'_`r_age'_ir`ir'_15 = .;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_15 = 2 if pv_paygo_97_15yr_`sex'_`r_age' > pv_ira_`yr'yr_`sex'_`r_age'_ir`ir'_n25;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_15 = 1 if pv_paygo_97_15yr_`sex'_`r_age' < pv_ira_`yr'yr_`sex'_`r_age'_ir`ir'_n25;
			};
		};
	};
};

** now are assuming 20 years of contributions, so no eligibility for minimum pension under new scheme;
** the difference for years is in the number of years under the new system;
foreach ir in 4 8 {;
	foreach r_age in norm early {;
		foreach sex in male female 77le 70le {;
			foreach yr in 5 10 15 20 {;
				gen choice_`yr'yr_`sex'_`r_age'_ir`ir'_20 = .;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_20 = 2 if pv_paygo_97_20yr_`sex'_`r_age' > pv_ira_`yr'yr_`sex'_`r_age'_ir`ir'_n25;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_20 = 1 if pv_paygo_97_20yr_`sex'_`r_age' < pv_ira_`yr'yr_`sex'_`r_age'_ir`ir'_n25;
			};
		};
	};
};


** now are assuming 25 years of contributions, so eligibility for minimum pension under new scheme;
** the difference for years is in the number of years under the new system;
foreach ir in 4 8 {;
	foreach r_age in norm early {;
		foreach sex in male female 77le 70le {;
			foreach yr in 5 10 15 20 25 {;
				gen choice_`yr'yr_`sex'_`r_age'_ir`ir'_25 = .;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_25 = 2 if pv_paygo_97_25yr_`sex'_`r_age' > pv_ira_`yr'yr_`sex'_`r_age'_ir`ir';
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_25 = 1 if pv_paygo_97_25yr_`sex'_`r_age' < pv_ira_`yr'yr_`sex'_`r_age'_ir`ir';
			};
		};
	};
};

** now are assuming 30 years of contributions, so eligibility for minimum pension under new scheme;
** the difference for years is in the number of years under the new system;
foreach ir in 4 8 {;
	foreach r_age in norm early {;
		foreach sex in male female 77le 70le {;
			foreach yr in 5 10 15 20 25 30 {;
				gen choice_`yr'yr_`sex'_`r_age'_ir`ir'_30 = .;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_30 = 2 if pv_paygo_97_30yr_`sex'_`r_age' > pv_ira_`yr'yr_`sex'_`r_age'_ir`ir';
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_30 = 1 if pv_paygo_97_30yr_`sex'_`r_age' < pv_ira_`yr'yr_`sex'_`r_age'_ir`ir';
			};
		};
	};
};

** now are assuming 35 years of contributions, so eligibility for minimum pension under new scheme;
** the difference for years is in the number of years under the new system;
foreach ir in 4 8 {;
	foreach r_age in norm early {;
		foreach sex in male female 77le 70le {;
			foreach yr in 5 10 15 20 25 30 35 {;
				gen choice_`yr'yr_`sex'_`r_age'_ir`ir'_35 = .;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_35 = 2 if pv_paygo_97_35yr_`sex'_`r_age' > pv_ira_`yr'yr_`sex'_`r_age'_ir`ir';
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_35 = 1 if pv_paygo_97_35yr_`sex'_`r_age' < pv_ira_`yr'yr_`sex'_`r_age'_ir`ir';
			};
		};
	};
};

** now are assuming 40 years of contributions, so eligibility for minimum pension under new scheme;
** the difference for years is in the number of years under the new system;
foreach ir in 4 8 {;
	foreach r_age in norm early {;
		foreach sex in male female 77le 70le {;
			foreach yr in 5 10 15 20 25 30 35 40 {;
				gen choice_`yr'yr_`sex'_`r_age'_ir`ir'_40 = .;
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_40 = 2 if pv_paygo_97_40yr_`sex'_`r_age' > pv_ira_`yr'yr_`sex'_`r_age'_ir`ir';
				replace choice_`yr'yr_`sex'_`r_age'_ir`ir'_40 = 2 if pv_paygo_97_40yr_`sex'_`r_age' < pv_ira_`yr'yr_`sex'_`r_age'_ir`ir';
			};
		};
	};
};


replace sal=round(sal,1);

save ${tmp}pension_simulation_h, replace;


*Transition tables with 25 years of contributions, using 97 rules;

foreach r_age in norm early {;
	foreach sex in male female 77le 70le {;

		preserve;

		keep sal pv_paygo_97_25yr_`sex'_`r_age' pv_ira_*yr_`sex'_`r_age'_ir4 pv_ira_*yr_`sex'_`r_age'_ir8;
		rename pv_paygo_97_25yr_`sex'_`r_age' pv_paygo_25yr_`sex'_`r_age';
		drop pv_*_30yr_* pv_*_35yr_* pv_*_40yr_*;
		forval yr=5/24 {;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir4 ir4_`yr'_pra;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir8 ir8_`yr'_pra;
			gen ir4_`yr'_pyg=pv_paygo_25yr_`sex'_`r_age';
			gen ir8_`yr'_pyg=pv_paygo_25yr_`sex'_`r_age';

		};
		drop pv*;
		reshape long ir4 ir8, i(sal) j(type) string;
		rename ir4 ir4_;
		rename ir8 ir8_;
		reshape wide ir4_ ir8_, i(type) j(sal);
		replace type="_0"+substr(type,2,1)+substr(type,3,4) if substr(type,3,1)=="_";
		gen years_pra=substr(type,2,2);
		destring years_pra, replace;
		gen years_paygo=25-years_pra;
		gen type_order=1 if substr(type,5,3)=="pra";
		replace type_order=2 if substr(type,5,3)=="pyg";
		gen type_s="PRA" if type_order==1;
		replace type_s="PAYGO" if type_order==2;
		gsort -years_pra type_order;
		tostring years_*, replace;
		keep if inlist(years_pra,"24","20","15","10","5");
		replace years_pra="" if type_order==2;
		replace years_paygo="" if type_order==2;


		format ir* %9.1f;

		listtex years_pra type_s ir8_43 ir8_100 ir8_200 ir8_300 ir8_500 ir8_1078
			using ${tables}tpensim25`sex'`r_age'_h_97.tex, rstyle(tabular) replace;;

		restore;
	};
};

*Transition tables with 25 years of contributions, using 97 indexing but 01 minimum;

foreach r_age in norm early {;
	foreach sex in male female 77le 70le {;

		preserve;

		keep sal pv_paygo_9701_25yr_`sex'_`r_age' pv_ira_*yr_`sex'_`r_age'_ir4 pv_ira_*yr_`sex'_`r_age'_ir8;
		rename pv_paygo_9701_25yr_`sex'_`r_age' pv_paygo_25yr_`sex'_`r_age';
		drop pv_*_30yr_* pv_*_35yr_* pv_*_40yr_*;
		forval yr=5/24 {;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir4 ir4_`yr'_pra;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir8 ir8_`yr'_pra;
			gen ir4_`yr'_pyg=pv_paygo_25yr_`sex'_`r_age';
			gen ir8_`yr'_pyg=pv_paygo_25yr_`sex'_`r_age';

		};
		drop pv*;
		reshape long ir4 ir8, i(sal) j(type) string;
		rename ir4 ir4_;
		rename ir8 ir8_;
		reshape wide ir4_ ir8_, i(type) j(sal);
		replace type="_0"+substr(type,2,1)+substr(type,3,4) if substr(type,3,1)=="_";
		gen years_pra=substr(type,2,2);
		destring years_pra, replace;
		gen years_paygo=25-years_pra;
		gen type_order=1 if substr(type,5,3)=="pra";
		replace type_order=2 if substr(type,5,3)=="pyg";
		gen type_s="PRA" if type_order==1;
		replace type_s="PAYGO" if type_order==2;
		gsort -years_pra type_order;
		tostring years_*, replace;
		keep if inlist(years_pra,"24","20","15","10","5");
		replace years_pra="" if type_order==2;
		replace years_paygo="" if type_order==2;


		format ir* %9.1f;

		listtex years_pra type_s ir8_43 ir8_100 ir8_200 ir8_300 ir8_500 ir8_1078
			using ${tables}tpensim25`sex'`r_age'_h_9701.tex, rstyle(tabular) replace;;

		restore;
	};
};


*Transition tables with 25 years of contributions, using 01 rules;

foreach r_age in norm early {;
	foreach sex in male female 77le 70le {;

		preserve;

		keep sal pv_paygo_01_25yr_`sex'_`r_age' pv_ira_*yr_`sex'_`r_age'_ir4 pv_ira_*yr_`sex'_`r_age'_ir8;
		rename pv_paygo_01_25yr_`sex'_`r_age' pv_paygo_25yr_`sex'_`r_age';
		drop pv_*_30yr_* pv_*_35yr_* pv_*_40yr_*;
		forval yr=5/24 {;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir4 ir4_`yr'_pra;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir8 ir8_`yr'_pra;
			gen ir4_`yr'_pyg=pv_paygo_25yr_`sex'_`r_age';
			gen ir8_`yr'_pyg=pv_paygo_25yr_`sex'_`r_age';

		};
		drop pv*;
		reshape long ir4 ir8, i(sal) j(type) string;
		rename ir4 ir4_;
		rename ir8 ir8_;
		reshape wide ir4_ ir8_, i(type) j(sal);
		replace type="_0"+substr(type,2,1)+substr(type,3,4) if substr(type,3,1)=="_";
		gen years_pra=substr(type,2,2);
		destring years_pra, replace;
		gen years_paygo=25-years_pra;
		gen type_order=1 if substr(type,5,3)=="pra";
		replace type_order=2 if substr(type,5,3)=="pyg";
		gen type_s="PRA" if type_order==1;
		replace type_s="PAYGO" if type_order==2;
		gsort -years_pra type_order;
		tostring years_*, replace;
		keep if inlist(years_pra,"24","20","15","10","5");
		replace years_pra="" if type_order==2;
		replace years_paygo="" if type_order==2;


		format ir* %9.1f;

		listtex years_pra type_s ir8_43 ir8_100 ir8_200 ir8_300 ir8_500 ir8_1078
			using ${tables}tpensim25`sex'`r_age'_h_01.tex, rstyle(tabular) replace;;

		restore;
	};
};

*Transition tables with 35 years of contributions, using 97 rules;

foreach r_age in norm early {;
	foreach sex in male female 77le 70le {;

		preserve;

		keep sal pv_paygo_97_35yr_`sex'_`r_age' pv_ira_*yr_`sex'_`r_age'_ir4 pv_ira_*yr_`sex'_`r_age'_ir8;
		rename pv_paygo_97_35yr_`sex'_`r_age' pv_paygo_35yr_`sex'_`r_age';
		drop pv_*_40yr_*;
		forval yr=5(5)35 {;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir4 ir4_`yr'_pra;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir8 ir8_`yr'_pra;
			gen ir4_`yr'_pyg=pv_paygo_35yr_`sex'_`r_age';
			gen ir8_`yr'_pyg=pv_paygo_35yr_`sex'_`r_age';

		};
		drop pv*;
		reshape long ir4 ir8, i(sal) j(type) string;
		rename ir4 ir4_;
		rename ir8 ir8_;
		reshape wide ir4_ ir8_, i(type) j(sal);
		replace type="_05"+substr(type,3,4) if substr(type,3,1)=="_";
		gen years_pra=substr(type,2,2);
		destring years_pra, replace;
		gen years_paygo=25-years_pra;
		gen type_order=1 if substr(type,5,3)=="pra";
		replace type_order=2 if substr(type,5,3)=="pyg";
		gen type_s="PRA" if type_order==1;
		replace type_s="PAYGO" if type_order==2;
		gsort -years_pra type_order;
		tostring years_*, replace;
		replace years_pra="" if type_order==2;
		replace years_paygo="" if type_order==2;

		format ir* %9.1f;

                gen agein1997 = 60 - real(years_pra) if years_pra~="";
                gen str2 agein1997_s = string(agein1997, "%9.0f") if agein1997~=.;

		listtex agein1997_s years_pra type_s ir8_43 ir8_100 ir8_200 ir8_300 ir8_500 ir8_1078
			using ${tables}tpensim35`sex'`r_age'_h_97.tex, rstyle(tabular) replace;;

		restore;
	};
};


*Transition tables with 35 years of contributions, using 97 indexing and 01 minimum pension;

foreach r_age in norm early {;
	foreach sex in male female 77le 70le {;

		preserve;

		keep sal pv_paygo_9701_35yr_`sex'_`r_age' pv_ira_*yr_`sex'_`r_age'_ir4 pv_ira_*yr_`sex'_`r_age'_ir8;
		rename pv_paygo_9701_35yr_`sex'_`r_age' pv_paygo_35yr_`sex'_`r_age';
		drop pv_*_40yr_*;
		forval yr=5(5)35 {;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir4 ir4_`yr'_pra;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir8 ir8_`yr'_pra;
			gen ir4_`yr'_pyg=pv_paygo_35yr_`sex'_`r_age';
			gen ir8_`yr'_pyg=pv_paygo_35yr_`sex'_`r_age';

		};
		drop pv*;
		reshape long ir4 ir8, i(sal) j(type) string;
		rename ir4 ir4_;
		rename ir8 ir8_;
		reshape wide ir4_ ir8_, i(type) j(sal);
		replace type="_05"+substr(type,3,4) if substr(type,3,1)=="_";
		gen years_pra=substr(type,2,2);
		destring years_pra, replace;
		gen years_paygo=25-years_pra;
		gen type_order=1 if substr(type,5,3)=="pra";
		replace type_order=2 if substr(type,5,3)=="pyg";
		gen type_s="PRA" if type_order==1;
		replace type_s="PAYGO" if type_order==2;
		gsort -years_pra type_order;
		tostring years_*, replace;
		replace years_pra="" if type_order==2;
		replace years_paygo="" if type_order==2;

		format ir* %9.1f;

                gen agein1997 = 60 - real(years_pra) if years_pra~="";
                gen str2 agein1997_s = string(agein1997, "%9.0f") if agein1997~=.;

		listtex agein1997_s years_pra type_s ir8_43 ir8_100 ir8_200 ir8_300 ir8_500 ir8_1078
			using ${tables}tpensim35`sex'`r_age'_h_9701.tex, rstyle(tabular) replace;

		restore;
	};
};
*Transition tables with 35 years of contributions, using 01 rules;

foreach r_age in norm early {;
	foreach sex in male female 77le 70le {;

		preserve;

		keep sal pv_paygo_01_35yr_`sex'_`r_age' pv_ira_*yr_`sex'_`r_age'_ir4 pv_ira_*yr_`sex'_`r_age'_ir8;
		rename pv_paygo_01_35yr_`sex'_`r_age' pv_paygo_35yr_`sex'_`r_age';
		drop pv_*_40yr_*;
		forval yr=5(5)35 {;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir4 ir4_`yr'_pra;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir8 ir8_`yr'_pra;
			gen ir4_`yr'_pyg=pv_paygo_35yr_`sex'_`r_age';
			gen ir8_`yr'_pyg=pv_paygo_35yr_`sex'_`r_age';

		};
		drop pv*;
		reshape long ir4 ir8, i(sal) j(type) string;
		rename ir4 ir4_;
		rename ir8 ir8_;
		reshape wide ir4_ ir8_, i(type) j(sal);
		replace type="_05"+substr(type,3,4) if substr(type,3,1)=="_";
		gen years_pra=substr(type,2,2);
		destring years_pra, replace;
		gen years_paygo=25-years_pra;
		gen type_order=1 if substr(type,5,3)=="pra";
		replace type_order=2 if substr(type,5,3)=="pyg";
		gen type_s="PRA" if type_order==1;
		replace type_s="PAYGO" if type_order==2;
		gsort -years_pra type_order;
		tostring years_*, replace;
		replace years_pra="" if type_order==2;
		replace years_paygo="" if type_order==2;

		format ir* %9.1f;

                gen agein1997 = 60 - real(years_pra) if years_pra~="";
                gen str2 agein1997_s = string(agein1997, "%9.0f") if agein1997~=.;


		listtex agein1997_s years_pra type_s ir8_43 ir8_100 ir8_200 ir8_300 ir8_500 ir8_1078
			using ${tables}tpensim35`sex'`r_age'_h_01.tex, rstyle(tabular) replace;;

		restore;
	};
};

*New generation tables, using 97 rules;

foreach r_age in norm early {;
	foreach sex in male female 77le 70le {;

		preserve;

		keep sal
		  pv_paygo_97_*yr_`sex'_`r_age'
		  pv_ira_5yr_`sex'_`r_age'_ir4_n25 pv_ira_5yr_`sex'_`r_age'_ir8_n25
		  pv_ira_10yr_`sex'_`r_age'_ir4_n25 pv_ira_10yr_`sex'_`r_age'_ir8_n25
		  pv_ira_10yr_`sex'_`r_age'_ir4_n25 pv_ira_10yr_`sex'_`r_age'_ir8_n25
		  pv_ira_15yr_`sex'_`r_age'_ir4_n25 pv_ira_15yr_`sex'_`r_age'_ir8_n25
		  pv_ira_20yr_`sex'_`r_age'_ir4_n25 pv_ira_20yr_`sex'_`r_age'_ir8_n25
		  pv_ira_25yr_`sex'_`r_age'_ir4 pv_ira_25yr_`sex'_`r_age'_ir8
		  pv_ira_30yr_`sex'_`r_age'_ir4 pv_ira_30yr_`sex'_`r_age'_ir8
		  pv_ira_35yr_`sex'_`r_age'_ir4 pv_ira_35yr_`sex'_`r_age'_ir8
		  pv_ira_40yr_`sex'_`r_age'_ir4 pv_ira_40yr_`sex'_`r_age'_ir8;
		forval yr=5(5)40 {;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir4 ir4_`yr'_pra;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir8 ir8_`yr'_pra;
			rename pv_paygo_97_`yr'yr_`sex'_`r_age' ir4_`yr'_pyg;
			gen ir8_`yr'_pyg = ir4_`yr'_pyg;

		};

		reshape long ir4 ir8, i(sal) j(type) string;
		rename ir4 ir4_;
		rename ir8 ir8_;
		reshape wide ir4_ ir8_, i(type) j(sal);
		replace type="_05"+substr(type,3,4) if substr(type,3,1)=="_";
		gen years_pra=substr(type,2,2);
		destring years_pra, replace;
		gen years_paygo=years_pra;
		gen type_order=1 if substr(type,5,3)=="pra";
		replace type_order=2 if substr(type,5,3)=="pyg";
		gen type_s="PRA" if type_order==1;
		replace type_s="PAYGO" if type_order==2;
		gsort -years_pra type_order;
		drop if years_pra==40;
		tostring years_*, replace;
		replace years_pra="" if type_order==2;
		replace years_paygo="" if type_order==2;

		format ir* %9.1f;

		listtex years_pra type_s ir8_43 ir8_100 ir8_200 ir8_300 ir8_500 ir8_1078
			using ${tables}tpensimnew`sex'`r_age'_h_97.tex, rstyle(tabular) replace;

		restore;
	};
};

*New generation tables, using 97 indexing and 01 minimum penison;

foreach r_age in norm early {;
	foreach sex in male female 77le 70le {;

		preserve;

		keep sal
		  pv_paygo_9701_*yr_`sex'_`r_age'
		  pv_ira_5yr_`sex'_`r_age'_ir4_n25 pv_ira_5yr_`sex'_`r_age'_ir8_n25
		  pv_ira_10yr_`sex'_`r_age'_ir4_n25 pv_ira_10yr_`sex'_`r_age'_ir8_n25
		  pv_ira_10yr_`sex'_`r_age'_ir4_n25 pv_ira_10yr_`sex'_`r_age'_ir8_n25
		  pv_ira_15yr_`sex'_`r_age'_ir4_n25 pv_ira_15yr_`sex'_`r_age'_ir8_n25
		  pv_ira_20yr_`sex'_`r_age'_ir4_n25 pv_ira_20yr_`sex'_`r_age'_ir8_n25
		  pv_ira_25yr_`sex'_`r_age'_ir4 pv_ira_25yr_`sex'_`r_age'_ir8
		  pv_ira_30yr_`sex'_`r_age'_ir4 pv_ira_30yr_`sex'_`r_age'_ir8
		  pv_ira_35yr_`sex'_`r_age'_ir4 pv_ira_35yr_`sex'_`r_age'_ir8
		  pv_ira_40yr_`sex'_`r_age'_ir4 pv_ira_40yr_`sex'_`r_age'_ir8;
		forval yr=5(5)40 {;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir4 ir4_`yr'_pra;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir8 ir8_`yr'_pra;
			rename pv_paygo_9701_`yr'yr_`sex'_`r_age' ir4_`yr'_pyg;
			gen ir8_`yr'_pyg = ir4_`yr'_pyg;

		};

		reshape long ir4 ir8, i(sal) j(type) string;
		rename ir4 ir4_;
		rename ir8 ir8_;
		reshape wide ir4_ ir8_, i(type) j(sal);
		replace type="_05"+substr(type,3,4) if substr(type,3,1)=="_";
		gen years_pra=substr(type,2,2);
		destring years_pra, replace;
		gen years_paygo=years_pra;
		gen type_order=1 if substr(type,5,3)=="pra";
		replace type_order=2 if substr(type,5,3)=="pyg";
		gen type_s="PRA" if type_order==1;
		replace type_s="PAYGO" if type_order==2;
		gsort -years_pra type_order;
		drop if years_pra==40;
		tostring years_*, replace;
		replace years_pra="" if type_order==2;
		replace years_paygo="" if type_order==2;

		format ir* %9.1f;

		listtex years_pra type_s ir8_43 ir8_100 ir8_200 ir8_300 ir8_500 ir8_1078
			using ${tables}tpensimnew`sex'`r_age'_h_9701.tex, rstyle(tabular) replace;

		restore;
	};
};

*New generation tables, using 01 rules;

foreach r_age in norm early {;
	foreach sex in male female 77le 70le {;

		preserve;

		keep sal
		  pv_paygo_01_*yr_`sex'_`r_age'
		  pv_ira_5yr_`sex'_`r_age'_ir4_n25 pv_ira_5yr_`sex'_`r_age'_ir8_n25
		  pv_ira_10yr_`sex'_`r_age'_ir4_n25 pv_ira_10yr_`sex'_`r_age'_ir8_n25
		  pv_ira_10yr_`sex'_`r_age'_ir4_n25 pv_ira_10yr_`sex'_`r_age'_ir8_n25
		  pv_ira_15yr_`sex'_`r_age'_ir4_n25 pv_ira_15yr_`sex'_`r_age'_ir8_n25
		  pv_ira_20yr_`sex'_`r_age'_ir4_n25 pv_ira_20yr_`sex'_`r_age'_ir8_n25
		  pv_ira_25yr_`sex'_`r_age'_ir4 pv_ira_25yr_`sex'_`r_age'_ir8
		  pv_ira_30yr_`sex'_`r_age'_ir4 pv_ira_30yr_`sex'_`r_age'_ir8
		  pv_ira_35yr_`sex'_`r_age'_ir4 pv_ira_35yr_`sex'_`r_age'_ir8
		  pv_ira_40yr_`sex'_`r_age'_ir4 pv_ira_40yr_`sex'_`r_age'_ir8;
		forval yr=5(5)40 {;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir4 ir4_`yr'_pra;
			rename pv_ira_`yr'yr_`sex'_`r_age'_ir8 ir8_`yr'_pra;
			rename pv_paygo_01_`yr'yr_`sex'_`r_age' ir4_`yr'_pyg;
			gen ir8_`yr'_pyg = ir4_`yr'_pyg;

		};

		reshape long ir4 ir8, i(sal) j(type) string;
		rename ir4 ir4_;
		rename ir8 ir8_;
		reshape wide ir4_ ir8_, i(type) j(sal);
		replace type="_05"+substr(type,3,4) if substr(type,3,1)=="_";
		gen years_pra=substr(type,2,2);
		destring years_pra, replace;
		gen years_paygo=years_pra;
		gen type_order=1 if substr(type,5,3)=="pra";
		replace type_order=2 if substr(type,5,3)=="pyg";
		gen type_s="PRA" if type_order==1;
		replace type_s="PAYGO" if type_order==2;
		gsort -years_pra type_order;
		drop if years_pra==40;
		tostring years_*, replace;
		replace years_pra="" if type_order==2;
		replace years_paygo="" if type_order==2;

		format ir* %9.1f;

		listtex years_pra type_s ir8_43 ir8_100 ir8_200 ir8_300 ir8_500 ir8_1078
			using ${tables}tpensimnew`sex'`r_age'_h_01.tex, rstyle(tabular) replace;

		restore;
	};
};


***************************************************************************************;
********** Table 2A (timsseneu1990): Comparing IMSS and ENEU/ENE datasets **********************;
***************************************************************************************;

local yr = 1990;
local month = 6;


*** make summary statistics for prepeared IMSS data in metro areas (i.e. output of prepareimsshh.do);

clear;
use year qtr nss rsal7net male age empl_imss using ${work}imss_forhh_`yr'`month', clear;

gen byte imss=1;

gen empl10plus = 0;
replace empl10plus = 1 if empl_imss>=10 & empl_imss~=.;
gen empl100plus = 0;
replace empl100plus = 1 if empl_imss>=100 & empl_imss~=.;

tempfile d3;
save `d3', replace;

foreach subsample in "male" "female"
{;

 clear;
 use `d3';

 if "`subsample'" == "male" keep if male==1;
 if "`subsample'" == "female" keep if male==0;

	foreach var in "rsal7net" "age" "empl10plus" "empl100plus"
	{;

	 mean `var';
	 local `var'_n = e(N);
	 mat b = e(b);
	 local `var'_mn = b[1,1];
	 estat sd;
	 mat sd = r(sd);
	 local `var'_sd = sd[1,1];
	 estat vce;
	 mat vce = r(V);
	 local `var'_se = sqrt(vce[1,1]);

         if "`var'"=="rsal7net"
         {;

          su `var', detail;
          local `var'_med = r(p50);

          * boostrap standard error of median;

          preserve;
          bootstrap r(p50), reps(200) seed(1234): su `var', detail;
          mat list e(se);
          mat se = e(se);
          local `var'_medse = se[1,1];

          di "`var'_medse = ``var'_medse'";
          restore;

         };

	};

	collapse (sum) n = imss;
	gen weight=n;

	foreach var in "rsal7net" "age" "empl10plus" "empl100plus"
	{;

	 gen `var'_mn = ``var'_mn';
	 gen `var'_sd = ``var'_sd';
	 gen `var'_se = ``var'_se';
         if "`var'"=="rsal7net"
         {;
          gen `var'_med = ``var'_med';
          gen `var'_medse = ``var'_medse';
         };
	};


	list;

	gen sample = "prepimss_`subsample'";

	save ${tmp}prepimss`yr'`month'stats_`subsample', replace;

};

*** make summary statistics for ENEU 1990;

clear;

use year qtr male age factor rsal* work
    work_imss work_ft_imss work_ft firmsize
    if year==1990 & qtr==2 using ${work}eneu_forimss_19882003;

gen byte eneu19882003=1;

gen empl10plus=0;
replace empl10plus=1 if inlist(firmsize,4,5,6,7,8);
gen empl100plus=0;
replace empl100plus=1 if inlist(firmsize,7,8);

* rename wage variable so it is the same as in IMSS;
gen rsal7net = rsal7;

tab work;

tempfile d4;
save `d4', replace;

foreach subsample in "all_male" "ft_male" "wimss_male" "fullwimss_male" "woimss_male"
 	"all_female" "ft_female" "wimss_female" "fullwimss_female" "woimss_female"
{;

 clear;
 use `d4';

 if "`subsample'" == "all_male" keep if male==1;
 if "`subsample'" == "ft_male" keep if male==1 & work_ft==1; * full-time workers;
 if "`subsample'" == "wimss_male" keep if male==1 & work_imss==1; * workers who report getting IMSS;
 if "`subsample'" == "fullwimss_male" keep if male==1 & work_ft_imss==1; * full-time workers who get IMSS;
 if "`subsample'" == "woimss_male" keep if male==1 & work_imss==0; * workers who do not get IMSS;
 if "`subsample'" == "all_female" keep if male==0;
 if "`subsample'" == "ft_female" keep if male==0 & work_ft==1; * full-time workers;
 if "`subsample'" == "wimss_female" keep if male==0 & work_imss==1; * workers who report getting IMSS;
 if "`subsample'" == "fullwimss_female" keep if male==0 & work_ft_imss==1; * full-time workers who get IMSS;
 if "`subsample'" == "woimss_female" keep if male==0 & work_imss==0; * workers who do not get IMSS;

 foreach var in "rsal7net" "age" "empl10plus" "empl100plus"
 {;

  svyset [pw=factor];
  svy:mean `var';
  local `var'_n = e(N);
  mat b = e(b);
  local `var'_mn = b[1,1];
  estat sd;
  mat sd = r(sd);
  local `var'_sd = sd[1,1];
  estat vce;
  mat vce = r(V);
  local `var'_se = sqrt(vce[1,1]);

  if "`var'"=="rsal7net"
  {;

   su `var' [aw=factor], detail;
   local `var'_med = r(p50);

   * boostrap standard error of median;

   preserve;
   expand factor;
   bootstrap r(p50), reps(200) seed(1234): su `var', detail;
   mat list e(se);
   mat se = e(se);
   local `var'_medse = se[1,1];

   di "`var'_medse = ``var'_medse'";
   restore;

  };

 };

 collapse (sum) n = eneu19882003 weight=factor;

 foreach var in "rsal7net" "age" "empl10plus" "empl100plus"
 {;

  gen `var'_mn = ``var'_mn';
  gen `var'_sd = ``var'_sd';
  gen `var'_se = ``var'_se';

  if "`var'"=="rsal7net"
  {;
   gen `var'_med = ``var'_med';
   gen `var'_medse = ``var'_medse';
  };

 };

 gen sample = "eneu_`subsample'";

 save ${tmp}eneu1990q2stats_`subsample', replace;

};



*** stack datasets from different samples;

clear;

use ${tmp}prepimss`yr'`month'stats_male;
append using ${tmp}eneu`yr'q2stats_wimss_male;
append using ${tmp}eneu`yr'q2stats_all_male;
append using ${tmp}eneu`yr'q2stats_ft_male;
append using ${tmp}eneu`yr'q2stats_fullwimss_male;
append using ${tmp}eneu`yr'q2stats_woimss_male;
append using ${tmp}prepimss`yr'`month'stats_female;
append using ${tmp}eneu`yr'q2stats_wimss_female;
append using ${tmp}eneu`yr'q2stats_all_female;
append using ${tmp}eneu`yr'q2stats_ft_female;
append using ${tmp}eneu`yr'q2stats_fullwimss_female;
append using ${tmp}eneu`yr'q2stats_woimss_female;

*** harmonize variables;

gen dailywage_mn = rsal7net_mn;
gen dailywage_se = rsal7net_se;
gen dailywage_med = rsal7net_med;
gen dailywage_medse = rsal7net_medse;

save ${tmp}timsseneu`yr'`month', replace;

*** make table;

clear;
use ${tmp}timsseneu`yr'`month';

keep sample dailywage_mn dailywage_se age* empl100plus* n* weight*;
drop *_sd;
order dailywage* age* empl* n* weight*;

* transpose;
rename sample _varname;
xpose, clear varname promote;
rename _varname rowvar;

foreach var in "prepimss_male" "prepimss_female"
 "eneu_all_male" "eneu_ft_male" "eneu_wimss_male" "eneu_woimss_male" "eneu_fullwimss_male"
 "eneu_all_female" "eneu_ft_female" "eneu_wimss_female" "eneu_woimss_female" "eneu_fullwimss_female"
{;

 gen str8 `var'_ = string(`var', "%12.2f");
 gen str8 `var'_s = string(`var', "%12.2f") if strmatch(rowvar,"*se")==0;
 replace `var'_s = "(" + `var'_ + ")" if strmatch(rowvar,"*se")==1;
 replace `var'_s = string(`var', "%12.0f") if rowvar=="n" | rowvar=="weight";
 *replace `var'_s = "(" + "``var'_s'" + ")" if strmatch(rowvar,"*_se");
};

gen str1 placeholder = " ";
gen str40 rowdesc = "";
replace rowdesc = "real avg. daily post-tax wage" if rowvar == "dailywage_mn";
*replace rowdesc = "real median daily post-tax wage" if rowvar == "dailywage_med";
replace rowdesc = "age" if rowvar == "age_mn";
replace rowdesc = "fraction employed in ests $>$100 employees" if rowvar == "empl100plus_mn";
replace rowdesc = "N (raw observations)" if rowvar == "n";
replace rowdesc = "N (population, using weights)" if rowvar == "weight";


listtex rowdesc placeholder prepimss_male_s eneu_all_male_s eneu_wimss_male_s
	eneu_woimss_male_s placeholder eneu_fullwimss_male_s
    using ${tables}timsseneu1990_male.tex, rstyle(tabular)
    replace;

listtex rowdesc placeholder prepimss_female_s eneu_all_female_s eneu_wimss_female_s
    eneu_woimss_female_s placeholder eneu_fullwimss_female_s
    using ${tables}timsseneu1990_female.tex, rstyle(tabular)
    replace;

***************************************************************************************;
********** Table 2B (timsseneu2000): Comparing IMSS and ENEU datasets, 2000 ******************;
***************************************************************************************;

local yr = 2000;
local month = 6;

*** make summary statistics for prepeared IMSS data in metro areas (i.e. output of prepareimsshh.do);

clear;
use year qtr nss rsal7net male age empl_imss using ${work}imss_forhh_`yr'`month', clear;

gen byte imss=1;

gen empl10plus = 0;
replace empl10plus = 1 if empl_imss>=10 & empl_imss~=.;
gen empl100plus = 0;
replace empl100plus = 1 if empl_imss>=100 & empl_imss~=.;

tempfile d2;
save `d2';

foreach subsample in "male" "female"
{;

 clear;
 use `d2';

 if "`subsample'" == "male" keep if male==1;
 if "`subsample'" == "female" keep if male==0;

	foreach var in "rsal7net" "age" "empl10plus" "empl100plus"
	{;

         mean `var';
	 local `var'_n = e(N);
	 mat b = e(b);
	 local `var'_mn = b[1,1];
	 estat sd;
	 mat sd = r(sd);
	 local `var'_sd = sd[1,1];
	 estat vce;
	 mat vce = r(V);
	 local `var'_se = sqrt(vce[1,1]);

         if "`var'"=="rsal7net"
         {;

          su `var', detail;
          local `var'_med = r(p50);

          * boostrap standard error of median;

          preserve;
          bootstrap r(p50), reps(200) seed(1234): su `var', detail;
          mat list e(se);
          mat se = e(se);
          local `var'_medse = se[1,1];

          di "`var'_medse = ``var'_medse'";
          restore;

         };
	};

	collapse (sum) n = imss;
	gen weight=n;

	foreach var in "rsal7net" "age" "empl10plus" "empl100plus"
	{;

	 gen `var'_mn = ``var'_mn';
	 gen `var'_sd = ``var'_sd';
	 gen `var'_se = ``var'_se';

         if "`var'"=="rsal7net"
         {;
          gen `var'_med = ``var'_med';
          gen `var'_medse = ``var'_medse';
        };
	};


	list;

	gen sample = "prepimss_`subsample'";

	save ${tmp}prepimss`yr'`month'stats_`subsample', replace;

};

*** make summary statistics for ENEU 2000;

clear;

use year qtr male age factor rsal* work
    work_imss work_perm_imss work_ft_imss firmsize
    if year==2000 & qtr==2 using ${work}eneu_forimss_19882003;

gen byte eneu19882003=1;

gen empl10plus=0;
replace empl10plus=1 if inlist(firmsize,4,5,6,7,8);
gen empl100plus=0;
replace empl100plus=1 if inlist(firmsize,7,8);

* rename wage variable so it is the same as in IMSS;
gen rsal7net = rsal7;

tab work;

tempfile d1;
save `d1', replace;

foreach subsample in "all_male" "wimss_male" "permwimss_male" "fullwimss_male" "woimss_male"
 	"all_female" "wimss_female" "permwimss_female" "fullwimss_female" "woimss_female"
{;

 clear;
 use `d1';

 if "`subsample'" == "all_male" keep if male==1;
 if "`subsample'" == "wimss_male" keep if male==1 & work_imss==1; * workers who report getting IMSS;
 if "`subsample'" == "permwimss_male" keep if male==1 & work_perm_imss==1; * perm workers who get IMSS;
 if "`subsample'" == "fullwimss_male" keep if male==1 & work_ft_imss==1; * full-time workers who get IMSS;
 if "`subsample'" == "woimss_male" keep if male==1 & work_imss==0; * workers who do not get IMSS;
 if "`subsample'" == "all_female" keep if male==0;
 if "`subsample'" == "wimss_female" keep if male==0 & work_imss==1; * workers who report getting IMSS;
 if "`subsample'" == "permwimss_female" keep if male==0 & work_perm_imss==1; * perm workers who get IMSS;
 if "`subsample'" == "fullwimss_female" keep if male==0 & work_ft_imss==1; * full-time workers who get IMSS;
 if "`subsample'" == "woimss_female" keep if male==0 & work_imss==0; * workers who do not get IMSS;

 foreach var in "rsal7net" "age" "empl10plus" "empl100plus"
 {;

  svyset [pw=factor];
  svy:mean `var';
  local `var'_n = e(N);
  mat b = e(b);
  local `var'_mn = b[1,1];
  estat sd;
  mat sd = r(sd);
  local `var'_sd = sd[1,1];
  estat vce;
  mat vce = r(V);
  local `var'_se = sqrt(vce[1,1]);

  if "`var'"=="rsal7net"
  {;

   su `var' [aw=factor], detail;
   local `var'_med = r(p50);

   * boostrap standard error of median;

   preserve;
   expand factor;
   bootstrap r(p50), reps(200) seed(1234): su `var', detail;
   mat list e(se);
   mat se = e(se);
   local `var'_medse = se[1,1];

   di "`var'_medse = ``var'_medse'";
   restore;

  };

 };

 collapse (sum) n = eneu19882003 weight=factor;

 foreach var in "rsal7net" "age" "empl10plus" "empl100plus"
 {;

  gen `var'_mn = ``var'_mn';
  gen `var'_sd = ``var'_sd';
  gen `var'_se = ``var'_se';

  if "`var'"=="rsal7net"
  {;
   gen `var'_med = ``var'_med';
   gen `var'_medse = ``var'_medse';
  };

 };

 gen sample = "eneu_`subsample'";

 save ${tmp}eneu2000q2stats_`subsample', replace;

};



*** stack datasets from different samples;

clear;

use ${tmp}prepimss`yr'`month'stats_male;
append using ${tmp}eneu`yr'q2stats_wimss_male;
append using ${tmp}eneu`yr'q2stats_all_male;
append using ${tmp}eneu`yr'q2stats_fullwimss_male;
append using ${tmp}eneu`yr'q2stats_permwimss_male;
append using ${tmp}eneu`yr'q2stats_woimss_male;
append using ${tmp}prepimss`yr'`month'stats_female;
append using ${tmp}eneu`yr'q2stats_wimss_female;
append using ${tmp}eneu`yr'q2stats_all_female;
append using ${tmp}eneu`yr'q2stats_fullwimss_female;
append using ${tmp}eneu`yr'q2stats_permwimss_female;
append using ${tmp}eneu`yr'q2stats_woimss_female;


*** harmonize variables;

gen dailywage_mn = rsal7net_mn;
gen dailywage_se = rsal7net_se;
gen dailywage_med = rsal7net_med;
gen dailywage_medse = rsal7net_medse;

save ${tmp}timsseneu`yr'`month', replace;

*** make table;

clear;
use ${tmp}timsseneu`yr'`month';

keep sample dailywage_mn dailywage_se age* empl100plus* n weight;
drop *_sd;
order dailywage* age* empl* n* weight*;

* transpose;
rename sample _varname;
xpose, clear varname promote;
rename _varname rowvar;

foreach var in "prepimss_male" "prepimss_female"
 "eneu_all_male" "eneu_wimss_male" "eneu_woimss_male" "eneu_permwimss_male" "eneu_fullwimss_male"
 "eneu_all_female" "eneu_wimss_female" "eneu_woimss_female" "eneu_permwimss_female" "eneu_fullwimss_female"
{;

 gen str8 `var'_ = string(`var', "%12.2f");
 gen str8 `var'_s = string(`var', "%12.2f") if strmatch(rowvar,"*se")==0;
 replace `var'_s = "(" + `var'_ + ")" if strmatch(rowvar,"*se")==1;
 replace `var'_s = string(`var', "%12.0f") if rowvar=="n" | rowvar=="weight";
 *replace `var'_s = "(" + "``var'_s'" + ")" if strmatch(rowvar,"*_se");
};

gen str1 placeholder = " ";
gen str40 rowdesc = "";
replace rowdesc = "real avg. daily post-tax wage" if rowvar == "dailywage_mn";
*replace rowdesc = "real median daily post-tax wage" if rowvar == "dailywage_med";
replace rowdesc = "age" if rowvar == "age_mn";
replace rowdesc = "fraction employed in ests $>$100 employees" if rowvar == "empl100plus_mn";
replace rowdesc = "N (raw observations)" if rowvar == "n";
replace rowdesc = "N (population, using weights)" if rowvar == "weight";

listtex rowdesc placeholder prepimss_male_s eneu_all_male_s eneu_wimss_male_s
	eneu_woimss_male_s eneu_permwimss_male_s eneu_fullwimss_male_s
    using ${tables}timsseneu2000_male.tex, rstyle(tabular)
    replace;

listtex rowdesc placeholder prepimss_female_s eneu_all_female_s eneu_wimss_female_s
    eneu_woimss_female_s eneu_permwimss_female_s eneu_fullwimss_female_s
    using ${tables}timsseneu2000_female.tex, rstyle(tabular)
    replace;




**********************************************************************************************;
*** Table 3 (tgapvsfssec2_male`male'_y_p`p'): cross-sectional patterns **************;
**********************************************************************************************;

local p = 15; * use 15th percentile in excess mass calculations below;

forvalues male = 0/1
{;


 clear;

 *** initialize table;

 local ncol = 11; * number of columns;
 local nrow_b = 11; * number of covariates to report;
 local nrow_d = 1; * number of rows of dummy variables;

 initializetable "tgapvsfssec2_male`male'_y_p`p'" `ncol' `nrow_b' 2 `nrow_d' 0 0 0
     " 'age 26-35' 'age 36-45' 'age 46-55' 'age 56-65' '11-50 employees'
      '51-100 employees' '101-250 employees' '\$>\$ 250 employees' 'construction' 'retail/services'
      'constant' "
     " 'metro area effects'
       'R-squared'  'N' ";

 drop _all;
 use ${work}cellpqymetrofssec_age_cat2_19882003;

 keep if year==1990;
 keep if male==`male';
 keep if qtr==2;

 *desc, fullnames;
 *su;

 merge 1:m year male metro_area firmsize3 age_cat2 sector using ${work}excmasspqyfs_metroagesec;
 tab _merge;
 keep if _merge==3;
 drop _merge;

 ** generate new variables;

 gen ldiffmednet_ft_imss = log(rsal7_eneu_ft_imss_med/rsal7net_imss_med);
 gen ldiffmednet_imss = log(rsal7_eneu_imss_med/rsal7net_imss_med);
 gen ldiffmednet_perm = log(rsal7_eneu_med_perm_imss/rsal7net_imss_med);

 gen ldiffmnnet_ft_imss = log(rsal7_eneu_ft_imss_mn/rsal7net_imss_mn);
 gen ldiffmnnet_imss = log(rsal7_eneu_imss_mn/rsal7net_imss_mn);
 gen ldiffmnnet_perm = log(rsal7_eneu_mn_perm_imss/rsal7net_imss_mn);

 keep if ldiffmednet_ft_imss~=. & ldiffmnnet_ft_imss~=. & excmass_p`p'~=.;

 xi i.age_cat2 i.metro_area i.firmsize3 i.sector;

 local col = 0;

 local col = `col'+1;
 areg ldiffmednet_ft_imss _Iage_cat2* [pw=empl_imss], robust absorb(year);

 matrix hold_b[1,`col'] = _b[_Iage_cat2_2];
 matrix hold_se[1,`col'] = _se[_Iage_cat2_2];
 genstars hold_b[1,`col'] hold_se[1,`col'] .1 .05 .01;
 matrix hold_stars[1,`col'] = r(nstars);

 matrix hold_b[2,`col'] = _b[_Iage_cat2_3];
 matrix hold_se[2,`col'] = _se[_Iage_cat2_3];
 genstars hold_b[2,`col'] hold_se[2,`col'] .1 .05 .01;
 matrix hold_stars[2,`col'] = r(nstars);

 matrix hold_b[3,`col'] = _b[_Iage_cat2_4];
 matrix hold_se[3,`col'] = _se[_Iage_cat2_4];
 genstars hold_b[3,`col'] hold_se[3,`col'] .1 .05 .01;
 matrix hold_stars[3,`col'] = r(nstars);

 matrix hold_b[4,`col'] = _b[_Iage_cat2_5];
 matrix hold_se[4,`col'] = _se[_Iage_cat2_5];
 genstars hold_b[4,`col'] hold_se[4,`col'] .1 .05 .01;
 matrix hold_stars[4,`col'] = r(nstars);

 matrix hold_b[11,`col'] = _b[_cons];
 matrix hold_se[11,`col'] = _se[_cons];
 genstars hold_b[11,`col'] hold_se[11,`col'] .1 .05 .01;
 matrix hold_stars[11,`col'] = r(nstars);

 matrix hold_e[1,`col'] = e(r2);
 matrix hold_e[2,`col'] = e(N);
 matrix hold_d[1,`col'] = 0;

 local col = `col'+1;
 areg ldiffmednet_ft_imss _Ifirmsize3* [pw=empl_imss], robust absorb(year);

 matrix hold_b[5,`col'] = _b[_Ifirmsize3_2];
 matrix hold_se[5,`col'] = _se[_Ifirmsize3_2];
 genstars hold_b[5,`col'] hold_se[5,`col'] .1 .05 .01;
 matrix hold_stars[5,`col'] = r(nstars);

 matrix hold_b[6,`col'] = _b[_Ifirmsize3_3];
 matrix hold_se[6,`col'] = _se[_Ifirmsize3_3];
 genstars hold_b[6,`col'] hold_se[6,`col'] .1 .05 .01;
 matrix hold_stars[6,`col'] = r(nstars);

 matrix hold_b[7,`col'] = _b[_Ifirmsize3_4];
 matrix hold_se[7,`col'] = _se[_Ifirmsize3_4];
 genstars hold_b[7,`col'] hold_se[7,`col'] .1 .05 .01;
 matrix hold_stars[7,`col'] = r(nstars);

 matrix hold_b[8,`col'] = _b[_Ifirmsize3_5];
 matrix hold_se[8,`col'] = _se[_Ifirmsize3_5];
 genstars hold_b[8,`col'] hold_se[8,`col'] .1 .05 .01;
 matrix hold_stars[8,`col'] = r(nstars);

 matrix hold_b[11,`col'] = _b[_cons];
 matrix hold_se[11,`col'] = _se[_cons];
 genstars hold_b[11,`col'] hold_se[11,`col'] .1 .05 .01;
 matrix hold_stars[11,`col'] = r(nstars);

 matrix hold_e[1,`col'] = e(r2);
 matrix hold_e[2,`col'] = e(N);
 matrix hold_d[1,`col'] = 0;

 local col = `col'+1;
 areg ldiffmednet_ft_imss _Ifirmsize3* _Iage_cat2* _Imetro* _Isector* [pw=empl_imss], robust absorb(year);

 matrix hold_b[1,`col'] = _b[_Iage_cat2_2];
 matrix hold_se[1,`col'] = _se[_Iage_cat2_2];
 genstars hold_b[1,`col'] hold_se[1,`col'] .1 .05 .01;
 matrix hold_stars[1,`col'] = r(nstars);

 matrix hold_b[2,`col'] = _b[_Iage_cat2_3];
 matrix hold_se[2,`col'] = _se[_Iage_cat2_3];
 genstars hold_b[2,`col'] hold_se[2,`col'] .1 .05 .01;
 matrix hold_stars[2,`col'] = r(nstars);

 matrix hold_b[3,`col'] = _b[_Iage_cat2_4];
 matrix hold_se[3,`col'] = _se[_Iage_cat2_4];
 genstars hold_b[3,`col'] hold_se[3,`col'] .1 .05 .01;
 matrix hold_stars[3,`col'] = r(nstars);

 matrix hold_b[4,`col'] = _b[_Iage_cat2_5];
 matrix hold_se[4,`col'] = _se[_Iage_cat2_5];
 genstars hold_b[4,`col'] hold_se[4,`col'] .1 .05 .01;
 matrix hold_stars[4,`col'] = r(nstars);

 matrix hold_b[5,`col'] = _b[_Ifirmsize3_2];
 matrix hold_se[5,`col'] = _se[_Ifirmsize3_2];
 genstars hold_b[5,`col'] hold_se[5,`col'] .1 .05 .01;
 matrix hold_stars[5,`col'] = r(nstars);

 matrix hold_b[6,`col'] = _b[_Ifirmsize3_3];
 matrix hold_se[6,`col'] = _se[_Ifirmsize3_3];
 genstars hold_b[6,`col'] hold_se[6,`col'] .1 .05 .01;
 matrix hold_stars[6,`col'] = r(nstars);

 matrix hold_b[7,`col'] = _b[_Ifirmsize3_4];
 matrix hold_se[7,`col'] = _se[_Ifirmsize3_4];
 genstars hold_b[7,`col'] hold_se[7,`col'] .1 .05 .01;
 matrix hold_stars[7,`col'] = r(nstars);

 matrix hold_b[8,`col'] = _b[_Ifirmsize3_5];
 matrix hold_se[8,`col'] = _se[_Ifirmsize3_5];
 genstars hold_b[8,`col'] hold_se[8,`col'] .1 .05 .01;
 matrix hold_stars[8,`col'] = r(nstars);

 matrix hold_b[7,`col'] = _b[_Ifirmsize3_4];
 matrix hold_se[7,`col'] = _se[_Ifirmsize3_4];
 genstars hold_b[7,`col'] hold_se[7,`col'] .1 .05 .01;
 matrix hold_stars[7,`col'] = r(nstars);

 matrix hold_b[8,`col'] = _b[_Ifirmsize3_5];
 matrix hold_se[8,`col'] = _se[_Ifirmsize3_5];
 genstars hold_b[8,`col'] hold_se[8,`col'] .1 .05 .01;
 matrix hold_stars[8,`col'] = r(nstars);

 matrix hold_b[9,`col'] = _b[_Isector_2];
 matrix hold_se[9,`col'] = _se[_Isector_2];
 genstars hold_b[9,`col'] hold_se[9,`col'] .1 .05 .01;
 matrix hold_stars[9,`col'] = r(nstars);

 matrix hold_b[10,`col'] = _b[_Isector_3];
 matrix hold_se[10,`col'] = _se[_Isector_3];
 genstars hold_b[10,`col'] hold_se[10,`col'] .1 .05 .01;
 matrix hold_stars[10,`col'] = r(nstars);

 matrix hold_b[11,`col'] = _b[_cons];
 matrix hold_se[11,`col'] = _se[_cons];
 genstars hold_b[11,`col'] hold_se[11,`col'] .1 .05 .01;
 matrix hold_stars[11,`col'] = r(nstars);

 matrix hold_e[1,`col'] = e(r2);
 matrix hold_e[2,`col'] = e(N);
 matrix hold_d[1,`col'] = 1;

 * add placeholder column;
 local col = `col'+1;

 local col = `col'+1;
 areg ldiffmnnet_ft_imss _Iage_cat2* [pw=empl_imss], robust absorb(year);

 matrix hold_b[1,`col'] = _b[_Iage_cat2_2];
 matrix hold_se[1,`col'] = _se[_Iage_cat2_2];
 genstars hold_b[1,`col'] hold_se[1,`col'] .1 .05 .01;
 matrix hold_stars[1,`col'] = r(nstars);

 matrix hold_b[2,`col'] = _b[_Iage_cat2_3];
 matrix hold_se[2,`col'] = _se[_Iage_cat2_3];
 genstars hold_b[2,`col'] hold_se[2,`col'] .1 .05 .01;
 matrix hold_stars[2,`col'] = r(nstars);

 matrix hold_b[3,`col'] = _b[_Iage_cat2_4];
 matrix hold_se[3,`col'] = _se[_Iage_cat2_4];
 genstars hold_b[3,`col'] hold_se[3,`col'] .1 .05 .01;
 matrix hold_stars[3,`col'] = r(nstars);

 matrix hold_b[4,`col'] = _b[_Iage_cat2_5];
 matrix hold_se[4,`col'] = _se[_Iage_cat2_5];
 genstars hold_b[4,`col'] hold_se[4,`col'] .1 .05 .01;
 matrix hold_stars[4,`col'] = r(nstars);

 matrix hold_b[11,`col'] = _b[_cons];
 matrix hold_se[11,`col'] = _se[_cons];
 genstars hold_b[11,`col'] hold_se[11,`col'] .1 .05 .01;
 matrix hold_stars[11,`col'] = r(nstars);

 matrix hold_e[1,`col'] = e(r2);
 matrix hold_e[2,`col'] = e(N);
 matrix hold_d[1,`col'] = 0;

 local col = `col'+1;
 areg ldiffmnnet_ft_imss _Ifirmsize3* [pw=empl_imss], robust absorb(year);

 matrix hold_b[5,`col'] = _b[_Ifirmsize3_2];
 matrix hold_se[5,`col'] = _se[_Ifirmsize3_2];
 genstars hold_b[5,`col'] hold_se[5,`col'] .1 .05 .01;
 matrix hold_stars[5,`col'] = r(nstars);

 matrix hold_b[6,`col'] = _b[_Ifirmsize3_3];
 matrix hold_se[6,`col'] = _se[_Ifirmsize3_3];
 genstars hold_b[6,`col'] hold_se[6,`col'] .1 .05 .01;
 matrix hold_stars[6,`col'] = r(nstars);

 matrix hold_b[7,`col'] = _b[_Ifirmsize3_4];
 matrix hold_se[7,`col'] = _se[_Ifirmsize3_4];
 genstars hold_b[7,`col'] hold_se[7,`col'] .1 .05 .01;
 matrix hold_stars[7,`col'] = r(nstars);

 matrix hold_b[8,`col'] = _b[_Ifirmsize3_5];
 matrix hold_se[8,`col'] = _se[_Ifirmsize3_5];
 genstars hold_b[8,`col'] hold_se[8,`col'] .1 .05 .01;
 matrix hold_stars[8,`col'] = r(nstars);

 matrix hold_b[11,`col'] = _b[_cons];
 matrix hold_se[11,`col'] = _se[_cons];
 genstars hold_b[11,`col'] hold_se[11,`col'] .1 .05 .01;
 matrix hold_stars[11,`col'] = r(nstars);

 matrix hold_e[1,`col'] = e(r2);
 matrix hold_e[2,`col'] = e(N);
 matrix hold_d[1,`col'] = 0;

 local col = `col'+1;
 areg ldiffmnnet_ft_imss _Ifirmsize3* _Iage_cat2* _Imetro* _Isector* [pw=empl_imss], robust absorb(year);

 matrix hold_b[1,`col'] = _b[_Iage_cat2_2];
 matrix hold_se[1,`col'] = _se[_Iage_cat2_2];
 genstars hold_b[1,`col'] hold_se[1,`col'] .1 .05 .01;
 matrix hold_stars[1,`col'] = r(nstars);

 matrix hold_b[2,`col'] = _b[_Iage_cat2_3];
 matrix hold_se[2,`col'] = _se[_Iage_cat2_3];
 genstars hold_b[2,`col'] hold_se[2,`col'] .1 .05 .01;
 matrix hold_stars[2,`col'] = r(nstars);

 matrix hold_b[3,`col'] = _b[_Iage_cat2_4];
 matrix hold_se[3,`col'] = _se[_Iage_cat2_4];
 genstars hold_b[3,`col'] hold_se[3,`col'] .1 .05 .01;
 matrix hold_stars[3,`col'] = r(nstars);

 matrix hold_b[4,`col'] = _b[_Iage_cat2_5];
 matrix hold_se[4,`col'] = _se[_Iage_cat2_5];
 genstars hold_b[4,`col'] hold_se[4,`col'] .1 .05 .01;
 matrix hold_stars[4,`col'] = r(nstars);

 matrix hold_b[5,`col'] = _b[_Ifirmsize3_2];
 matrix hold_se[5,`col'] = _se[_Ifirmsize3_2];
 genstars hold_b[5,`col'] hold_se[5,`col'] .1 .05 .01;
 matrix hold_stars[5,`col'] = r(nstars);

 matrix hold_b[6,`col'] = _b[_Ifirmsize3_3];
 matrix hold_se[6,`col'] = _se[_Ifirmsize3_3];
 genstars hold_b[6,`col'] hold_se[6,`col'] .1 .05 .01;
 matrix hold_stars[6,`col'] = r(nstars);

 matrix hold_b[7,`col'] = _b[_Ifirmsize3_4];
 matrix hold_se[7,`col'] = _se[_Ifirmsize3_4];
 genstars hold_b[7,`col'] hold_se[7,`col'] .1 .05 .01;
 matrix hold_stars[7,`col'] = r(nstars);

 matrix hold_b[8,`col'] = _b[_Ifirmsize3_5];
 matrix hold_se[8,`col'] = _se[_Ifirmsize3_5];
 genstars hold_b[8,`col'] hold_se[8,`col'] .1 .05 .01;
 matrix hold_stars[8,`col'] = r(nstars);


 matrix hold_b[9,`col'] = _b[_Isector_2];
 matrix hold_se[9,`col'] = _se[_Isector_2];
 genstars hold_b[9,`col'] hold_se[9,`col'] .1 .05 .01;
 matrix hold_stars[9,`col'] = r(nstars);

 matrix hold_b[10,`col'] = _b[_Isector_3];
 matrix hold_se[10,`col'] = _se[_Isector_3];
 genstars hold_b[10,`col'] hold_se[10,`col'] .1 .05 .01;
 matrix hold_stars[10,`col'] = r(nstars);

 matrix hold_b[11,`col'] = _b[_cons];
 matrix hold_se[11,`col'] = _se[_cons];
 genstars hold_b[11,`col'] hold_se[11,`col'] .1 .05 .01;
 matrix hold_stars[11,`col'] = r(nstars);

 matrix hold_e[1,`col'] = e(r2);
 matrix hold_e[2,`col'] = e(N);
 matrix hold_d[1,`col'] = 1;

 * add placeholder column;
 local col = `col'+1;

  local col = `col'+1;
 areg excmass_p`p' _Iage_cat2* [pw=empl_imss], robust absorb(year);

 matrix hold_b[1,`col'] = _b[_Iage_cat2_2];
 matrix hold_se[1,`col'] = _se[_Iage_cat2_2];
 genstars hold_b[1,`col'] hold_se[1,`col'] .1 .05 .01;
 matrix hold_stars[1,`col'] = r(nstars);

 matrix hold_b[2,`col'] = _b[_Iage_cat2_3];
 matrix hold_se[2,`col'] = _se[_Iage_cat2_3];
 genstars hold_b[2,`col'] hold_se[2,`col'] .1 .05 .01;
 matrix hold_stars[2,`col'] = r(nstars);

 matrix hold_b[3,`col'] = _b[_Iage_cat2_4];
 matrix hold_se[3,`col'] = _se[_Iage_cat2_4];
 genstars hold_b[3,`col'] hold_se[3,`col'] .1 .05 .01;
 matrix hold_stars[3,`col'] = r(nstars);

 matrix hold_b[4,`col'] = _b[_Iage_cat2_5];
 matrix hold_se[4,`col'] = _se[_Iage_cat2_5];
 genstars hold_b[4,`col'] hold_se[4,`col'] .1 .05 .01;
 matrix hold_stars[4,`col'] = r(nstars);

 matrix hold_b[11,`col'] = _b[_cons];
 matrix hold_se[11,`col'] = _se[_cons];
 genstars hold_b[11,`col'] hold_se[11,`col'] .1 .05 .01;
 matrix hold_stars[11,`col'] = r(nstars);

 matrix hold_e[1,`col'] = e(r2);
 matrix hold_e[2,`col'] = e(N);
 matrix hold_d[1,`col'] = 0;

 local col = `col'+1;
 areg excmass_p`p' _Ifirmsize3* [pw=empl_imss], robust absorb(year);

 matrix hold_b[5,`col'] = _b[_Ifirmsize3_2];
 matrix hold_se[5,`col'] = _se[_Ifirmsize3_2];
 genstars hold_b[5,`col'] hold_se[5,`col'] .1 .05 .01;
 matrix hold_stars[5,`col'] = r(nstars);

 matrix hold_b[6,`col'] = _b[_Ifirmsize3_3];
 matrix hold_se[6,`col'] = _se[_Ifirmsize3_3];
 genstars hold_b[6,`col'] hold_se[6,`col'] .1 .05 .01;
 matrix hold_stars[6,`col'] = r(nstars);

 matrix hold_b[7,`col'] = _b[_Ifirmsize3_4];
 matrix hold_se[7,`col'] = _se[_Ifirmsize3_4];
 genstars hold_b[7,`col'] hold_se[7,`col'] .1 .05 .01;
 matrix hold_stars[7,`col'] = r(nstars);

 matrix hold_b[8,`col'] = _b[_Ifirmsize3_5];
 matrix hold_se[8,`col'] = _se[_Ifirmsize3_5];
 genstars hold_b[8,`col'] hold_se[8,`col'] .1 .05 .01;
 matrix hold_stars[8,`col'] = r(nstars);

 matrix hold_b[11,`col'] = _b[_cons];
 matrix hold_se[11,`col'] = _se[_cons];
 genstars hold_b[11,`col'] hold_se[11,`col'] .1 .05 .01;
 matrix hold_stars[11,`col'] = r(nstars);

 matrix hold_e[1,`col'] = e(r2);
 matrix hold_e[2,`col'] = e(N);
 matrix hold_d[1,`col'] = 0;


 local col = `col'+1;
 areg excmass_p`p' _Ifirmsize3* _Iage_cat2* _Imetro* _Isector* [pw=empl_imss], robust absorb(year);

 matrix hold_b[1,`col'] = _b[_Iage_cat2_2];
 matrix hold_se[1,`col'] = _se[_Iage_cat2_2];
 genstars hold_b[1,`col'] hold_se[1,`col'] .1 .05 .01;
 matrix hold_stars[1,`col'] = r(nstars);

 matrix hold_b[2,`col'] = _b[_Iage_cat2_3];
 matrix hold_se[2,`col'] = _se[_Iage_cat2_3];
 genstars hold_b[2,`col'] hold_se[2,`col'] .1 .05 .01;
 matrix hold_stars[2,`col'] = r(nstars);

 matrix hold_b[3,`col'] = _b[_Iage_cat2_4];
 matrix hold_se[3,`col'] = _se[_Iage_cat2_4];
 genstars hold_b[3,`col'] hold_se[3,`col'] .1 .05 .01;
 matrix hold_stars[3,`col'] = r(nstars);

 matrix hold_b[4,`col'] = _b[_Iage_cat2_5];
 matrix hold_se[4,`col'] = _se[_Iage_cat2_5];
 genstars hold_b[4,`col'] hold_se[4,`col'] .1 .05 .01;
 matrix hold_stars[4,`col'] = r(nstars);

 matrix hold_b[5,`col'] = _b[_Ifirmsize3_2];
 matrix hold_se[5,`col'] = _se[_Ifirmsize3_2];
 genstars hold_b[5,`col'] hold_se[5,`col'] .1 .05 .01;
 matrix hold_stars[5,`col'] = r(nstars);

 matrix hold_b[6,`col'] = _b[_Ifirmsize3_3];
 matrix hold_se[6,`col'] = _se[_Ifirmsize3_3];
 genstars hold_b[6,`col'] hold_se[6,`col'] .1 .05 .01;
 matrix hold_stars[6,`col'] = r(nstars);

 matrix hold_b[7,`col'] = _b[_Ifirmsize3_4];
 matrix hold_se[7,`col'] = _se[_Ifirmsize3_4];
 genstars hold_b[7,`col'] hold_se[7,`col'] .1 .05 .01;
 matrix hold_stars[7,`col'] = r(nstars);

 matrix hold_b[8,`col'] = _b[_Ifirmsize3_5];
 matrix hold_se[8,`col'] = _se[_Ifirmsize3_5];
 genstars hold_b[8,`col'] hold_se[8,`col'] .1 .05 .01;
 matrix hold_stars[8,`col'] = r(nstars);

 matrix hold_b[9,`col'] = _b[_Isector_2];
 matrix hold_se[9,`col'] = _se[_Isector_2];
 genstars hold_b[9,`col'] hold_se[9,`col'] .1 .05 .01;
 matrix hold_stars[9,`col'] = r(nstars);

 matrix hold_b[10,`col'] = _b[_Isector_3];
 matrix hold_se[10,`col'] = _se[_Isector_3];
 genstars hold_b[10,`col'] hold_se[10,`col'] .1 .05 .01;
 matrix hold_stars[10,`col'] = r(nstars);

 matrix hold_b[11,`col'] = _b[_cons];
 matrix hold_se[11,`col'] = _se[_cons];
 genstars hold_b[11,`col'] hold_se[11,`col'] .1 .05 .01;
 matrix hold_stars[11,`col'] = r(nstars);

 matrix hold_e[1,`col'] = e(r2);
 matrix hold_e[2,`col'] = e(N);
 matrix hold_d[1,`col'] = 1;

 *** call program to make table for latex;
 mat2table `ncol' `nrow_b' "tgapvsfssec2_male`male'_y_p`p'" "%12.3f" "%12.3f" "%12.2f"
     "%12.0f" "%12.0f" `nrow_d';

};


********************************************************************************************************;
** Table 4 (tdivmetro3_male`male'_y_p`p'_o97): wage divergence, disagg by metro areas, omit 1997 **;
********************************************************************************************************;


local p = 15;

forvalues male = 0/1
{;


clear;

*** initialize table;

local ncol = 5; * number of columns;
local nrow_b = 15; * number of covariates to report;
local nrow_d = 2; * number of rows of dummy variables;

initializetable "tdivmetro3_male`male'_y_p`p'_o97" `ncol' `nrow_b' 2 `nrow_d' 0 0 0
     " '1(age \$<=\$ 45)*1988' '1(age \$<=\$ 45)*1989'
      '1(age \$<=\$ 45)*1990' '1(age \$<=\$ 45)*1991' '1(age \$<=\$ 45)*1992' '1(age \$<=\$ 45)*1993'
      '1(age \$<=\$ 45)*1994' '1(age \$<=\$ 45)*1995' '1(age \$<=\$ 45)*1996' '1(age \$<=\$ 45)*1998'
      '1(age \$<=\$ 45)*1999' '1(age \$<=\$ 45)*2000' '1(age \$<=\$ 45)*2001' '1(age \$<=\$ 45)*2002'
      '1(age \$<=\$ 45)*2003'"
     "  'age group-metro area effects' 'metro-year effects'
       'R-squared'  'N' ";

drop _all;
use ${work}cellpqymetro_age_cat2_19882003;

keep if qtr==2;
keep if male==`male';

 merge 1:m year male metro_area age_cat2 using ${work}excmasspqy_metro;
 tab _merge;
 keep if _merge==3;
 drop _merge;

** generate new variables;

gen str6 metroyear = string(metro_area)+string(year);
gen str5 ageyear = string(age_cat2)+string(year);
gen str3 agemet = string(age_cat2)+string(metro_area);

gen byte age_cat4 = 0 if inlist(age_cat2,1,2,3,4);
replace age_cat4 = 1 if inlist(age_cat2,5);

gen byte age_cat5 = 1-age_cat4;

gen byte age_cat6 = 1 if inlist(age_cat2,1,2,3);
replace age_cat6 = 0 if inlist(age_cat2,4, 5);

gen ldiffmednet_ft_imss = log(rsal7_eneu_ft_imss_med/rsal7net_imss_med);
gen ldiffmednet_imss = log(rsal7_eneu_imss_med/rsal7net_imss_med);
gen ldiffmednet_perm = log(rsal7_eneu_med_perm_imss/rsal7net_imss_med);

gen ldiffmnnet_ft_imss = log(rsal7_eneu_ft_imss_mn/rsal7net_imss_mn);
gen ldiffmnnet_imss = log(rsal7_eneu_imss_mn/rsal7net_imss_mn);
gen ldiffmnnet_perm = log(rsal7_eneu_mn_perm_imss/rsal7net_imss_mn);

gen empl_imss_1988_ = empl_imss if year==1988;
sort metro_area age_cat2 year;
egen empl_imss_1988 = max(empl_imss), by(metro_area age_cat2);
drop empl_imss_1988_;

 keep if ldiffmednet_ft_imss~=. & ldiffmnnet_ft_imss~=. & excmass_p`p'~=.;

* use age_cat6;

xi i.year i.year*age_cat6 i.age_cat2 i.agemet, noomit;
drop _Iyear_1997 _IyeaXage_1997 _Iage_cat2_1 _Iagemet_1;

local col = 0;

local col = `col'+1;
    areg ldiffmednet_ft_imss _IyeaXage* _Iagemet* [pw=empl_imss], robust absorb(metroyear);

forvalues i = 1/15
{;
 if `i'<=9 local yr = 1987+`i';
 if `i'>=10 local yr = 1987+`i'+1;
 matrix hold_b[`i',`col'] = _b[_IyeaXage_`yr'];
 matrix hold_se[`i',`col'] = _se[_IyeaXage_`yr'];
 genstars hold_b[`i',`col'] hold_se[`i',`col'] .1 .05 .01;
 matrix hold_stars[`i',`col'] = r(nstars);
};
matrix hold_e[1,`col'] = e(r2);
matrix hold_e[2,`col'] = e(N);
matrix hold_d[1,`col'] = 1;
matrix hold_d[2,`col'] = 1;

* save estimates for figure;
preserve;
mat b = e(b);
mat Vdiag = vecdiag(e(V));
mat list b;
mat list Vdiag;
mat fdivmed_`male'_y_p`p' = (b[1,1..15] \ Vdiag[1,1..15]);
mat list fdivmed_`male'_y_p`p';
drop _all;
svmat fdivmed_`male'_y_p`p', names(matcol);
gen str1 var = "b" if _n==1;
replace var = "Vdiag" if _n==2;
save ${work}fdivmed_`male'_y_p`p'_o97.dta, replace;
restore;

* make placeholder column;
local col = `col'+1;

local col = `col'+1;
areg ldiffmnnet_ft_imss _IyeaXage* _Iagemet* [pw=empl_imss], robust absorb(metroyear);
forvalues i = 1/15
{;
 if `i'<=9 local yr = 1987+`i';
 if `i'>=10 local yr = 1987+`i'+1;
 matrix hold_b[`i',`col'] = _b[_IyeaXage_`yr'];
 matrix hold_se[`i',`col'] = _se[_IyeaXage_`yr'];
 genstars hold_b[`i',`col'] hold_se[`i',`col'] .1 .05 .01;
 matrix hold_stars[`i',`col'] = r(nstars);
};
matrix hold_e[1,`col'] = e(r2);
matrix hold_e[2,`col'] = e(N);
matrix hold_d[1,`col'] = 1;
matrix hold_d[2,`col'] = 1;

* save estimates for figure;
preserve;
mat b = e(b);
mat Vdiag = vecdiag(e(V));
mat list b;
mat list Vdiag;
mat fdivmn_`male'_y_p`p' = (b[1,1..15] \ Vdiag[1,1..15]);
mat list fdivmn_`male'_y_p`p';
drop _all;
svmat fdivmn_`male'_y_p`p', names(matcol);
gen str1 var = "b" if _n==1;
replace var = "Vdiag" if _n==2;
save ${work}fdivmn_`male'_y_p`p'_o97.dta, replace;
restore;

* make placeholder column;
local col = `col'+1;

local col = `col'+1;
areg excmass_p`p' _IyeaXage* _Iagemet* [pw=empl_imss], robust absorb(metroyear);
forvalues i = 1/15
{;
 if `i'<=9 local yr = 1987+`i';
 if `i'>=10 local yr = 1987+`i'+1;
 matrix hold_b[`i',`col'] = _b[_IyeaXage_`yr'];
 matrix hold_se[`i',`col'] = _se[_IyeaXage_`yr'];
 genstars hold_b[`i',`col'] hold_se[`i',`col'] .1 .05 .01;
 matrix hold_stars[`i',`col'] = r(nstars);
};
matrix hold_e[1,`col'] = e(r2);
matrix hold_e[2,`col'] = e(N);
matrix hold_d[1,`col'] = 1;
matrix hold_d[2,`col'] = 1;

* save estimates for figure;
preserve;
mat b = e(b);
mat Vdiag = vecdiag(e(V));
mat list b;
mat list Vdiag;
mat fdivexm_`male'_y_p`p' = (b[1,1..15] \ Vdiag[1,1..15]);
mat list fdivexm_`male'_y_p`p';
drop _all;
svmat fdivexm_`male'_y_p`p', names(matcol);
gen str1 var = "b" if _n==1;
replace var = "Vdiag" if _n==2;
save ${work}fdivexm_`male'_y_p`p'_o97.dta, replace;
restore;


*** call program to make table for latex;
mat2table `ncol' `nrow_b' "tdivmetro3_male`male'_y_p`p'_o97" "%12.3f" "%12.3f" "%12.2f" "%12.0f" "%12.0f" `nrow_d';

};


********************************************************************;
********************************************************************;
************ Published appendix tables *****************************;
********************************************************************;
********************************************************************;


********************************************************************;
************ Tables A1 & A2 (tcontrib): contribution schedule  *****;
********************************************************************;

clear;
use ${raw}imss_employer_contrib_tjk_2012_04_15.dta;

foreach var of varlist sem-limit_cpda {;
  rename `var' `var'_e;
};
foreach var of varlist sem_e-srt_e {;
  gen str6 `var'_s = string(`var', "%9.3f");
};
foreach var of varlist limit* {;
  gen str2 `var'_s = string(`var', "%9.0f");
};

sort start_date;

tempfile d1;
save `d1';

use ${raw}imss_worker_contrib_tjk_2012_04_15.dta;

foreach var of varlist sem-limit_cpda {;
  rename `var' `var'_w;
};
foreach var of varlist sem_w-srt_w {;
  gen str6 `var'_s = string(`var', "%9.3f");
};
foreach var of varlist limit* {;
  gen str2 `var'_s = string(`var', "%9.0f");
};

sort start_date;

tempfile d2;
save `d2';

use ${raw}imss_govt_contrib_tjk_2012_04_15.dta;

foreach var of varlist sem-limit_cpda {;
  rename `var' `var'_g;
};
foreach var of varlist sem_g-srt_g {;
  gen str6 `var'_s = string(`var', "%9.3f");
};
foreach var of varlist limit* {;
  gen str2 `var'_s = string(`var', "%9.0f");
};

sort start_date;

tempfile d3;
save `d3';

use `d1';
merge start_date using `d2';
tab _merge;
drop _merge;
sort start_date;
merge start_date using `d3';
tab _merge;
drop _merge;

gen end_date=start_date[_n+1]-1;
gen date_range=string(start_date, "%tdMon_DD_CCYY") + " - " + string(end_date, "%tdMon_DD_CCYY");

** Combining these contributions allows us to be consistent in the pre and post
   reform;
gen siv_pra_e = srcv_r_e + srcv_c_e;
gen siv_pra_w = srcv_r_w + srcv_c_w;
gen siv_pra_g = srcv_r_g + srcv_c_g;
gen siv_nonpra_e = siv_e + aport_esp_e + sivcm_e;
gen siv_nonpra_w = siv_w + aport_esp_w + sivcm_w;
gen siv_nonpra_g = siv_g + aport_esp_g + sivcm_g;
gen sem_total_e = sem_e + sem_d_e;
gen sem_total_w = sem_w + sem_d_w;
gen sem_total_g = sem_g + sem_d_g;

foreach var of varlist siv_pra_e-sem_total_g {;
  gen str6 `var'_s = string(`var', "%9.3f");
};

listtex date_range
  sivcm_e_s sivcm_w_s sivcm_g_s limit_siv_e_s
  srcv_r_e_s srcv_r_w_s srcv_r_g_s limit_srcv_r_e_s if start_date<mdy(7,1,1997)
  using ${tables}tcontrib73_97_1.tex, rstyle(tabular) replace;

listtex date_range
  sem_e_s sem_w_s sem_g_s limit_sem_e_s
  sgps_e_s sgps_w_s sgps_g_s limit_sgps_e_s if start_date<mdy(7,1,1997)
  using ${tables}tcontrib73_97_2.tex, rstyle(tabular) replace;

listtex date_range
siv_pra_e_s siv_pra_w_s siv_pra_g_s limit_srcv_r_e_s
  siv_nonpra_e_s siv_nonpra_w_s siv_nonpra_g_s limit_srcv_c_e_s
  sgps_e_s sgps_w_s sgps_g_s limit_sgps_e_s if start_date>=mdy(7,1,1997)
  using ${tables}tcontrib97_07_1.tex, rstyle(tabular) replace;

listtex date_range
  sem_d_e_s sem_d_w_s sem_d_g_s limit_sem_e_s
  sem_fija_e_s sem_fija_w_s sem_fija_g_s limit_sem_e_s
  extra_e_s extra_w_s extra_g_s limit_sem_e_s if start_date>=mdy(7,1,1997)
  using ${tables}tcontrib97_07_2.tex, rstyle(tabular) replace;

listtex date_range
  siv_nonpra_e_s siv_nonpra_w_s siv_nonpra_g_s limit_srcv_c_e_s
  siv_pra_e_s siv_pra_w_s siv_pra_g_s limit_srcv_r_e_s
  sgps_e_s sgps_w_s sgps_g_s limit_sgps_e_s
  using ${tables}tcontrib73_07_pen.tex, rstyle(tabular) replace;

listtex date_range
  sem_total_e_s sem_total_w_s sem_total_g_s limit_sem_e_s
  sem_fija_e_s sem_fija_w_s sem_fija_g_s limit_sem_e_s
  extra_e_s extra_w_s extra_g_s limit_sem_e_s
  using ${tables}tcontrib73_07_health.tex, rstyle(tabular) replace;

********************************************************************;
************ Table A3 (tpensched): pension benefit shedule *********************;
********************************************************************;


use ${raw}imss_benefits_sched_ev_2011_09_26, clear;

tostring min_mw max_mw, replace force;

gen min_s=substr(min_mw,1,4);

replace min_s=substr(min_s,1,3)+"1" if substr(min_s,4,1)=="0";
replace min_s=substr(min_s,1,3)+"6" if substr(min_s,4,1)=="5";

gen range=min_s+" to "+max_mw;

replace range="1 or less" if range=="0 to 1";
replace range="6.01 or higher" if range=="6.01 to .";

format %9.2f avg;
format %9.3f add_yr;

listtex range avg add_yr using ${tables}tpensched.tex, rstyle(tabular) replace;

************************************************************************************************;
*************** Table A4 (tinfl_min_wage): inflation and minimum wage table *************************;
************************************************************************************************;

clear;
use ${work}minwages_wtopcodes.dta, clear;

keep if zona=="A";
keep if qtr==2;
keep year rsalmin salmin cpi;
gen infl_rate=((cpi - cpi[_n-1]) / cpi[_n-1])*100;
drop cpi;
keep if year>1982;
gen infl_rate_s=string(infl_rate,"%9.2f");
gen rsalmin_s=string(rsalmin,"%3.2f");
listtex year infl_rate_s rsalmin_s
 	using ${tables}tinfl_min_wage.tex, rstyle(tabular) replace;

****************************************************************************;
****** Table A6A (tagevsfs_imss_`male'_`yr'): age vs firm size cross-tabs, from IMSS ****;
****************************************************************************;

local month = 6;

foreach yr in 1990 1993 1997 2000 2003
{;

 forvalues male = 0/1
 {;

clear;
use year qtr nss rsal7net male age empl_imss firmsize3 age_cat2
     using ${work}imss_forhh_`yr'`month', clear;

keep if male == `male';

collapse  (count) n = rsal7net, by(firmsize3 age_cat2);

reshape wide n, i(firmsize3) j(age_cat2);

gen n_fs = n1+n2+n3+n4+n5;

forvalues i = 1/5
{;
 gen age_cat2_`i'_perc = (n`i'/n_fs)*100;
};

egen n_fs_sum = total(n_fs);
gen fs_perc = (n_fs/n_fs_sum)*100;

gen str1 placeholder = " ";
gen str40 rowdesc = "1-10 employees" if firmsize3==1;
replace rowdesc = "11-50 employees" if firmsize3==2;
replace rowdesc = "51-100 employees" if firmsize3==3;
replace rowdesc = "101-250 employees" if firmsize3==4;
replace rowdesc = "\$ > \$ 250 employees" if firmsize3==5;

foreach var of varlist age* fs_perc
{;
 gen str8 `var'_s = string(`var', "%12.1f");
};

list rowdesc placeholder age*_s placeholder fs_perc_s;

listtex rowdesc placeholder age*_s placeholder fs_perc_s
    using ${tables}tagevsfs_imss_`male'_`yr'.tex, rstyle(tabular)
    replace;

collapse (sum) n1-n5 n_fs;

forvalues i = 1/5
{;
 gen age_cat2_`i'_perc = (n`i'/n_fs)*100;
};

foreach var of varlist age*
{;
 gen str8 `var'_s = string(`var', "%12.1f");
};

gen str1 placeholder = " ";
gen str40 rowdesc = "all firm sizes";

list rowdesc placeholder age*_s placeholder placeholder;

listtex rowdesc placeholder age*_s placeholder placeholder
    using ${tables}tagevsfs_tot_imss_`male'_`yr'.tex, rstyle(tabular)
    replace;

};
};

****************************************************************************;
****** Table A6B (tagevsfs_eneu_`male'_`yr'_n): age vs firm size cross-tabs, from ENEU ****;
****************************************************************************;

local month = 6;

foreach yr in 1990 1993 1997 2000 2003
{;
forvalues male = 0/1
 {;

clear;
use year qtr rsal7 male age firmsize3 age_cat2 work_ft_imss factor
     using ${work}eneu_forimss_19882003, clear;

keep if year==`yr';
keep if male == `male';
keep if work_ft_imss==1; * full-time workers with IMSS coverage (baseline sample);

   count;
   di "dropping if missing firmsize variable";
   keep if firmsize3~=.;
   count;

    keep if qtr==2;

collapse  (count) n = rsal7 [pw=factor], by(firmsize3 age_cat2);

reshape wide n, i(firmsize3) j(age_cat2);

gen n_fs = n1+n2+n3+n4+n5;

forvalues i = 1/5
{;
 gen age_cat2_`i'_perc = (n`i'/n_fs)*100;
};

egen n_fs_sum = total(n_fs);
gen fs_perc = (n_fs/n_fs_sum)*100;

gen str1 placeholder = " ";
gen str40 rowdesc = "1-10 employees" if firmsize3==1;
replace rowdesc = "11-50 employees" if firmsize3==2;
replace rowdesc = "51-100 employees" if firmsize3==3;
replace rowdesc = "101-250 employees" if firmsize3==4;
replace rowdesc = "\$ > \$ 250 employees" if firmsize3==5;

foreach var of varlist age* fs_perc
{;
 gen str8 `var'_s = string(`var', "%12.1f");
};

list rowdesc placeholder age*_s placeholder fs_perc_s;

listtex rowdesc placeholder age*_s placeholder fs_perc_s
    using ${tables}tagevsfs_eneu_`male'_`yr'_n.tex, rstyle(tabular)
    replace;

collapse (sum) n1-n5 n_fs;

forvalues i = 1/5
{;
 gen age_cat2_`i'_perc = (n`i'/n_fs)*100;
};

foreach var of varlist age*
{;
 gen str8 `var'_s = string(`var', "%12.1f");
};

gen str1 placeholder = " ";
gen str40 rowdesc = "all firm sizes";

list rowdesc placeholder age*_s placeholder placeholder;

listtex rowdesc placeholder age*_s placeholder placeholder
    using ${tables}tagevsfs_tot_eneu_`male'_`yr'_n.tex, rstyle(tabular)
    replace;

};
};


**********************************************************************************************;
** Table A7 (templdiv3_male`male'_n_o97): employment divergence, disagg by metro areas, omit 1997 **;
**********************************************************************************************;

forvalues male = 0/1
{;


clear;

*** initialize table;

local ncol = 2; * number of columns;
local nrow_b = 15; * number of covariates to report;
local nrow_d = 3; * number of rows of dummy variables;

initializetable "templdiv3_male`male'_n_o97" `ncol' `nrow_b' 2 `nrow_d' 0 0 0
     " '1(age \$<=\$ 45)*1988' '1(age \$<=\$ 45)*1989'
      '1(age \$<=\$ 45)*1990' '1(age \$<=\$ 45)*1991' '1(age \$<=\$ 45)*1992' '1(age \$<=\$ 45)*1993'
      '1(age \$<=\$ 45)*1994' '1(age \$<=\$ 45)*1995' '1(age \$<=\$ 45)*1996' '1(age \$<=\$ 45)*1998'
      '1(age \$<=\$ 45)*1999' '1(age \$<=\$ 45)*2000' '1(age \$<=\$ 45)*2001' '1(age \$<=\$ 45)*2002'
      '1(age \$<=\$ 45)*2003'"
     "  'age group effects' 'age group-metro area effects' 'metro-year effects'
       'R-squared'  'N' ";

drop _all;
use ${work}cellpqnmetro_age_cat2_19882003;

keep if year<2003 | (year==2003 & qtr<=2);
keep if qtr==2;
keep if male==`male';

  keep if qtr==2;



** generate new variables;

gen str6 metroyear = string(metro_area)+string(year);
gen str5 ageyear = string(age_cat2)+string(year);
gen str3 agemet = string(age_cat2)+string(metro_area);

gen byte age_cat6 = 1 if inlist(age_cat2,1,2,3);
replace age_cat6 = 0 if inlist(age_cat2,4,5);

 gen ldiffempl_ft_imss = log(empl_eneu_ft_imss/empl_imss);

 keep if ldiffempl_ft_imss~=.;

* use age_cat6;

xi i.year i.year*age_cat6 i.age_cat2 i.agemet, noomit;
drop _Iyear_1997 _IyeaXage_1997 _Iage_cat2_1 _Iagemet_1;

local col = 0;

local col = `col'+1;
areg ldiffempl_ft_imss _IyeaXage* _Iage_cat2* [pw=empl_imss], robust absorb(metroyear);
forvalues i = 1/15
{;
 if `i'<=9 local yr = 1987+`i';
 if `i'>=10 local yr = 1987+`i'+1;
 matrix hold_b[`i',`col'] = _b[_IyeaXage_`yr'];
 matrix hold_se[`i',`col'] = _se[_IyeaXage_`yr'];
 genstars hold_b[`i',`col'] hold_se[`i',`col'] .1 .05 .01;
 matrix hold_stars[`i',`col'] = r(nstars);
};
matrix hold_e[1,`col'] = e(r2);
matrix hold_e[2,`col'] = e(N);
matrix hold_d[1,`col'] = 1;
matrix hold_d[2,`col'] = 0;
matrix hold_d[3,`col'] = 1;


local col = `col'+1;
areg ldiffempl_ft_imss _IyeaXage* _Iagemet* [pw=empl_imss], robust absorb(metroyear);
forvalues i = 1/15
{;
 if `i'<=9 local yr = 1987+`i';
 if `i'>=10 local yr = 1987+`i'+1;
 matrix hold_b[`i',`col'] = _b[_IyeaXage_`yr'];
 matrix hold_se[`i',`col'] = _se[_IyeaXage_`yr'];
 genstars hold_b[`i',`col'] hold_se[`i',`col'] .1 .05 .01;
 matrix hold_stars[`i',`col'] = r(nstars);
};
matrix hold_e[1,`col'] = e(r2);
matrix hold_e[2,`col'] = e(N);
matrix hold_d[1,`col'] = .;
matrix hold_d[2,`col'] = 1;
matrix hold_d[3,`col'] = 1;

*** call program to make table for latex;
mat2table `ncol' `nrow_b' "templdiv3_male`male'_n_o97" "%12.3f" "%12.3f" "%12.2f" "%12.0f" "%12.0f" `nrow_d';

};






log close;




